通过在where子句中使用术语进行排序

Question

我有一个简单的选择查询-

SELECT ID, NAME 
FROM PERSONS
WHERE NAME IN ('BBB', 'AAA', 'ZZZ')
-- ORDER BY ???

我希望此结果按提供名称的顺序排序，也就是说，结果集中的第一行应该是名称= BBB的那一行，第二行是AAA，第三行是ZZZ。

在SQL Server中这可能吗？我想知道如果有一种简单且简短的方法（例如大约5到6行代码）来做到这一点。

Answer 1

您可以创建一个有序的拆分函数：

CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item 
    FROM (SELECT Number, Item = SUBSTRING(@List, Number, 
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);

然后略微更改您的输入（一个逗号分隔的列表，而不是三个单独的字符串）：

SELECT p.ID, p.NAME 
FROM dbo.PERSONS AS p
INNER JOIN dbo.SplitStrings_Ordered('BBB,AAA,ZZZ', ',') AS s
ON p.NAME = s.Item
ORDER BY s.[Index];