拆分列表时确定第n个项目？

Question

我想ROW_NUMBER在cte中提供一个能够从具有给定索引（如array-indexer
arr[index]）的序列中选择特定项目的功能。当然，“序列”是一个来自表值函数（Split）的表。

那么如何在索引不可用时创建索引呢？这是查询：

DECLARE @string VARCHAR(100); 
SET @string='field1;field2;field3;field4;field5;field6;field7'; 
DECLARE @index INT; 
SET @index = 4;

WITH cte 
     AS (SELECT item, 
                rn=Row_number() 
                     OVER( 
                       ORDER BY item) 
         FROM   dbo.Split(@string, ';')) 
SELECT TOP 1 item 
FROM   cte 
WHERE  rn = @index

Row_number() OVER(ORDER BY item) 按字母顺序排序，但实际上我想按（不可用）索引排序。

请注意，这个问题是我对这个问题的回答引起的。

Answer 1

当然，您只需要跟踪每个项目在列表中的显示位置即可。一种方法是让您的拆分函数插入一IDENTITY列。以我的回答从你引用的问题：

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS @t TABLE([Index] INT IDENTITY(1,1), Item NVARCHAR(255))
AS
BEGIN
    INSERT @t(Item) SELECT SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ORDER BY Number OPTION (MAXDOP 1);

    RETURN;
END
GO

DECLARE @x TABLE(i INT, string NVARCHAR(4000));

INSERT @x SELECT 1, N'field1;field2;field3;field4;field5;'
UNION ALL SELECT 2, N'x;y;6;r;3;2;w;'
UNION ALL SELECT 3, N'ttt;444;rrr;333;111;444;777;888;';

SELECT x.i, s1.Item
FROM @x AS x
CROSS APPLY dbo.SplitStrings(x.string, ';') AS s1
WHERE s1.[Index] = 4;

内联替代方案，在规模上 可能 会 稍微 友好一些：

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

当然，最终您将需要修复架构。如果此列表中的第4个元素是重要数据，则应将其单独存储。