sql-server - 带有行号的 STRING_SPLIT

Question

这是我想要制作的:

Row_Num Person                  Value   Row_Number
  1        Leo                   Math   1
  1        Leo                Science   2
  1        Leo                History   3
  1        Leo   Math,Science,History   4
  2     Robert                    Gym   2
  2     Robert                   Math   3
  2     Robert                History   4
  2     Robert       Gym,Math,History   1
  3      David                    Art   1
  3      David                Science   2
  3      David               English    3
  3      David               History    4
  3      David       Computer Science   5
  3      David  Art,Science,English,History,Computer Science    6

这是我使用的代码:

with part_1 as
(
    select
        1 as [Row_Num],
        'Leo' as [Person],
        'Math,Science,History' as [Subjects]
    ---
    union
    ---
    select
        '2',
        'Robert',
        'Gym,Math,History'
    ---
    union
    ---
    select
        '3',
        'David',
        'Art,Science,English,History,Computer Science'
    ---
)
----------------------------------------------------------------------
select
    [Row_Num],
    [Person],
    [Subjects]
into
    #part1
from
    part_1;
go
--------------------------------------------------------------------------------
with p_2 as(

    select
        [Row_Num],
        [Person],
        --[Subjects],
        [value]

    from
        #part1
    cross apply
        STRING_SPLIT([Subjects],',')

    union all

    select
        [Row_Num],
        [Person],
        [Subjects]
    from
        #part1
    

)

select
     [Row_Num]
    ,[Person]
    ,[Value]
    ,row_number()
        over(Partition by Row_Num order by (select 1)) as [Row_Number]
from
    p_2
order by
     [Row_Num]
    ,[Row_Number]

这是我正在制作的:

Row_Num Person  Value   Row_Number
1   Leo Math    1
1   Leo Science 2
1   Leo History 3
1   Leo Math,Science,History    4
2   Robert  Gym,Math,History    1
2   Robert  Gym 2
2   Robert  Math    3
2   Robert  History 4
3   David   Art 1
3   David   Science 2
3   David   English 3
3   David   History 4
3   David   Computer Science    5
3   David   Art,Science,English,History,Computer Science    6

看起来不错，直到您看到罗伯特。所有主题都在第一行，而不是底部。

有什么建议吗？

Answer 1

STRING_SPLIT 是 documented不“关心”序数位置:

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).

如果数据的顺序位置很重要，请不要使用 STRING_SPLIT。就个人而言，我建议使用 delimitedsplit8k_LEAD ，其中包括 itemnumber 列。

但理想情况下，真正的解决方案是停止在数据库中存储分隔数据。再创建 2 个表，一个包含科目列表，另一个创建学生和科目之间的关系。

---

请注意，SQL Server 2022 为 STRING_SPLIT 带来了一个名为 ordinal 的新参数，当将 1 传递给它时，将导致 STRING_SPLIT 返回一个附加列(称为 ordinal)，其中包含值在字符串中的序号位置；因此您可以将该列添加到您的 ORDER BY 以确保保持排序。

当然，这不会改变这样一个事实，即您不应该一开始就存储分隔数据，并且仍然应该以修复您的设计为目标。