logo标签列表

Microsoft SQL Server 中的自然排序（人类字符和数字混合排序）

sqlsql-serversortingnatural-sort
8
感谢您抽出时间阅读这篇文章，内容很多！非常感谢所有的同好们！
如何进行自然排序？
即按字母数字组合数据的顺序排列，如下：
Season 1, Season 2, Season 10, Season 20

而不是

Season 1, Season 10, Season 2, Season 20

我会用一个非常实用的电视季节案例来说明，这是一种非常实用的格式。
我希望实现以下目标：
  1. 分享我的解决方案给其他人
  2. 请你帮忙想办法缩短它（或找到更好的解决方案）
  3. 您能解决下面的第7个标准吗？
我花了大约2个小时在网上研究，另外3个小时构建这个解决方案。其中一些参考资料来自：

在SO和其他网站上找到的一些解决方案只适用于90％的情况。但是，如果您的文本中有多个数字值，大多数/全部都不起作用，或者如果在文本中找不到数字，则会导致SQL错误。

我创建了这个SQLFiddle链接来进行测试（包括下面的所有代码）。

这是创建语句：

create table tvseason
(
    title varchar(100)
);

insert into tvseason (title)
values ('100 Season 03'), ('100 Season 1'),
       ('100 Season 10'), ('100 Season 2'),
       ('100 Season 4'), ('Show Season 1 (2008)'),
       ('Show Season 2 (2008)'), ('Show Season 10 (2008)'),
       ('Another Season 01'), ('Another Season 02'),
       ('Another 1st Anniversary Season 01'),
       ('Another 2nd Anniversary Season 01'),
       ('Another 10th Anniversary Season 01'),
       ('Some Show Another No Season Number'),
       ('Some Show No Season Number'),
       ('Show 2 Season 1'),
       ('Some Show With Season Number 1'),
       ('Some Show With Season Number 2'),
       ('Some Show With Season Number 10');

这是我的工作解决方案（只是无法解决以下标准＃7）：

select 
    title, "index", titleLeft,
    convert(int, coalesce(nullif(titleRightTrim2, ''), titleRight)) titleRight
from
    (select 
         title, "index", titleLeft, titleRight, titleRightTrim1,
         case 
            when PATINDEX('%[^0-9]%', titleRightTrim2) = 0 
               then titleRightTrim2
               else left(titleRightTrim2, PATINDEX('%[^0-9]%', titleRightTrim2) - 1)
         end as titleRightTrim2
     from
         (select
              title, 
              len(title) - PATINDEX('%[0-9] %', reverse(title)) 'index',
              left(title, len(title) - PATINDEX('%[0-9] %', reverse(title))) titleLeft,
              ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))) titleRight,
              ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))) titleRightTrim1,
              left(ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))), PATINDEX('% %', ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))))) titleRightTrim2
          from
              tvseason) x) y
order by 
    titleLeft, titleRight

需要考虑的标准:

  1. 文本不包含数字
  2. 文本以数字开头和结尾
  3. 文本只以数字开头
  4. 文本只以数字结尾
  5. 文本可能以(YYYY)结尾
  6. 文本可能以单个数字或双位数字结尾（例如1或01）
  7. 可选：以上任意组合，加上文本中间的数字

以下是输出结果：

title
100 Season 1
100 Season 2
100 Season 03
100 Season 4
100 Season 10
**Case 7 here**
Another 10th Anniversary Season 01
Another 1st Anniversary Season 01
Another 2nd Anniversary Season 01
Another Season 01
Another Season 02
Show (2008) Season 1
Show (2008) Season 2
Show 2 The 75th Anniversary Season 1
Show Season 1 (2008)
Show Season 2 (2008)
Show Season 10 (2008)
Some Show Another No Season Number
Some Show No Season Number
Some Show With Season Number 1
Some Show With Season Number 2
Some Show With Season Number 10
4个回答
5

我认为这个方法可以解决问题...... 它只是识别从非数字到数字的变化。尽管我没有进行大规模测试，但它应该还是相当快的。

SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

ALTER FUNCTION dbo.tfn_SplitForSort
/* ===================================================================
11/11/2018 JL, Created: Comments    
=================================================================== */
--===== Define I/O parameters
(
    @string VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN 
    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
        cte_Tally (n) AS (
            SELECT TOP (LEN(@string))
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
            FROM
                cte_n2 a CROSS JOIN cte_n2 b
            ),
        cte_split_string AS (
            SELECT 
                col_num = ROW_NUMBER() OVER (ORDER BY t.n) + CASE WHEN LEFT(@string, 1) LIKE '[0-9]' THEN 0 ELSE 1 END,
                string_part = SUBSTRING(@string, t.n, LEAD(t.n, 1, 8000) OVER (ORDER BY t.n) - t.n)
            FROM
                cte_Tally t
                CROSS APPLY ( VALUES (SUBSTRING(@string, t.n, 2)) ) s (str2)
            WHERE 
                t.n = 1
                OR SUBSTRING(@string, t.n - 1, 2) LIKE '[0-9][^0-9]'
                OR SUBSTRING(@string, t.n - 1, 2) LIKE '[^0-9][0-9]'
            )

    SELECT 
        so_01 = ISNULL(MAX(CASE WHEN ss.col_num = 1 THEN CONVERT(FLOAT, ss.string_part) END), 99999999),
        so_02 = MAX(CASE WHEN ss.col_num = 2 THEN ss.string_part END),
        so_03 = MAX(CASE WHEN ss.col_num = 3 THEN CONVERT(FLOAT, ss.string_part) END),
        so_04 = MAX(CASE WHEN ss.col_num = 4 THEN ss.string_part END),
        so_05 = MAX(CASE WHEN ss.col_num = 5 THEN CONVERT(FLOAT, ss.string_part) END),
        so_06 = MAX(CASE WHEN ss.col_num = 6 THEN ss.string_part END),
        so_07 = MAX(CASE WHEN ss.col_num = 7 THEN CONVERT(FLOAT, ss.string_part) END),
        so_08 = MAX(CASE WHEN ss.col_num = 8 THEN ss.string_part END),
        so_09 = MAX(CASE WHEN ss.col_num = 9 THEN CONVERT(FLOAT, ss.string_part) END),
        so_10 = MAX(CASE WHEN ss.col_num = 10 THEN ss.string_part END)
    FROM
        cte_split_string ss;
GO

The function in use...

SELECT 
    ts.*
FROM
    #tvseason ts
    CROSS APPLY dbo.tfn_SplitForSort (ts.title) sfs
ORDER BY
    sfs.so_01,
    sfs.so_02,
    sfs.so_03,
    sfs.so_04,
    sfs.so_05,
    sfs.so_06,
    sfs.so_07,
    sfs.so_08,
    sfs.so_09,
    sfs.so_10;

结果：

id          title
----------- ------------------------------------------
2           100 Season 1
4           100 Season 2
1           100 Season 03
5           100 Season 4
3           100 Season 10
11          Another 1st Anniversary Season 01
12          Another 2nd Anniversary Season 01
13          Another 10th Anniversary Season 01
9           Another Season 01
10          Another Season 02
16          Show 2 Season 1
6           Show Season 1 (2008)
7           Show Season 2 (2008)
8           Show Season 10 (2008)
14          Some Show Another No Season Number
15          Some Show No Season Number
17          Some Show With Season Number 1
18          Some Show With Season Number 2
19          Some Show With Season Number 10

--=====================================================================

[编辑于2020-09-23] 我回顾了一些我以前的帖子，当我遇到这个问题时，我想看看是否可以使用单个值输出。将10列添加到ORDER BY中只是笨拙的... 经过一番思考，我想到将FLOAT转换为BINARY，再将BINARY转换为VARCHAR，我可以使用STRING_AGG()函数重新组合字符串，从而得到所需的排序结果。 

CREATE FUNCTION dbo.human_sort_string
/* ===================================================================
09/23/2020 JL, Created: Just a test 
=================================================================== */
--===== Define I/O parameters
(
    @string varchar(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    WITH 
        cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
        cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
        cte_Tally (n) AS (
            SELECT TOP (LEN(@string))
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
            FROM
                cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
            ),
        cte_Parsed AS (
            SELECT 
                t.n,
                parsed_val = SUBSTRING(@string, ISNULL(NULLIF(t.n, 1), 0) + 1, LEAD(t.n, 1, 8000) OVER (ORDER BY t.n) - ISNULL(NULLIF(t.n, 1), 0))
            FROM 
                cte_Tally t
                CROSS APPLY ( VALUES (SUBSTRING(@string, t.n, 2)) ) sv (sub_val)
            WHERE 
                t.n = 1
                OR 
                sv.sub_val LIKE '[0-9][^0-9]'
                OR 
                sv.sub_val LIKE '[^0-9][0-9]'
            )
    SELECT 
        sort_string = STRING_AGG(ISNULL(CONVERT(varchar(8000), CONVERT(binary(8), TRY_CONVERT(float, p.parsed_val)), 2), p.parsed_val), '') WITHIN GROUP (ORDER BY p.n)
    FROM
        cte_Parsed p;
GO

现在，外部查询看起来像这样...
SELECT 
    ts.id,
    td.title
FROM
    #tvseason ts
    CROSS APPLY dbo.human_sort_string(ts.title) hss
ORDER BY
    hss.sort_string;

实际结果与先前的函数相同。
连接包含空值的表会返回错误。为了完整起见，也许应该检查空字符串？ - Masoud R
2

这个问题的要求比较复杂，无法通过简单的查询来实现。我的解决方案如下: 首先，我创建了一个示例数据，该数据将在此查询中使用。

CREATE TABLE #TVSEASON (TITLE VARCHAR(100));
INSERT INTO #TVSEASON (TITLE) VALUES 
('100'),
('100 SEASON 03'),
('100 SEASON 1'),
('100 SEASON 10'),
('100 SEASON 2'),
('100 SEASON 4'),
('SHOW (2008) SEASON 1'),
('SHOW (2008) SEASON 2'),
('SHOW SEASON 1 (2008)'),
('SHOW SEASON 2 (2008)'),
('SHOW SEASON 10 (2008)'),
('ANOTHER 1ST ANNIVERSARY SEASON 01'),
('ANOTHER 2ND ANNIVERSARY SEASON 01'),
('ANOTHER 10TH ANNIVERSARY SEASON 01'),
('ANOTHER SEASON 01'),
('ANOTHER SEASON 02'),
('SOME SHOW ANOTHER NO SEASON NUMBER'),
('SOME SHOW NO SEASON NUMBER'),
('SHOW 2 THE 75TH ANNIVERSARY SEASON 1'),
('SOME SHOW WITH SEASON NUMBER 1'),
('SOME SHOW WITH SEASON NUMBER 2'),
('SOME SHOW WITH SEASON NUMBER 10')

为了达到预期的结果，我创建了一个函数来从文本中分离出所有的单词和数字。 （注：在修剪掉1st中的空格后，我还通过函数删除了2nd等缩写。为了保险起见，如果用户误输入了空格，则可以将LTRIM从该函数中删除。但是如果您认为没有错误的可能性，则可以删除该函数中的LTRIM，因为它也会删除带有"th"的文本，例如“1 the title”将被转换为“1 e title”）。
--CREATE SPLIT FUNCTION
CREATE FUNCTION [dbo].[SplitAlphaNumeric]
(
    @LIST NVARCHAR(2000)
) 
RETURNS @RTNVALUE TABLE
(

    ID INT IDENTITY(1,1),
    WORDS NVARCHAR(100),
    NUMBERS INT
)
AS 
BEGIN
    WHILE (PATINDEX('%[0-9]%',@LIST) > 0)
    BEGIN
        INSERT INTO @RTNVALUE (WORDS, NUMBERS)
        SELECT  CASE    WHEN PATINDEX('%[0-9]%',@LIST) = 0 THEN @LIST
                        WHEN (PATINDEX('%[0-9]%',@LIST) = 1 AND PATINDEX('%[^0-9]%',@LIST) = 0) THEN ''
                        WHEN PATINDEX('%[0-9]%',@LIST) = 1 THEN ''
                        ELSE SUBSTRING(@LIST, 1, PATINDEX('%[0-9]%',@LIST) - 1) 
                END,
                CASE    WHEN PATINDEX('%[0-9]%',@LIST) = 0 THEN NULL
                        WHEN (PATINDEX('%[0-9]%',@LIST) = 1 AND PATINDEX('%[^0-9]%',@LIST) = 0) THEN CAST(LTRIM(RTRIM(@LIST)) AS INT)
                        WHEN PATINDEX('%[0-9]%',@LIST) = 1 THEN SUBSTRING(@LIST, 1, PATINDEX('%[^0-9]%',@LIST) - 1) 
                        ELSE NULL
                END

            SET @LIST = LTRIM(RTRIM(CASE    WHEN PATINDEX('%[0-9]%',@LIST) = 0 OR (PATINDEX('%[0-9]%',@LIST) = 1 AND PATINDEX('%[^0-9]%',@LIST) = 0) THEN ''
                                            WHEN PATINDEX('%[0-9]%',@LIST) = 1 THEN 
                                                    CASE    WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'ST%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
                                                            WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'ND%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
                                                            WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'RD%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
                                                            WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'TH%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
                                                            ELSE LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))
                                                    END
                                            ELSE SUBSTRING(@LIST, PATINDEX('%[0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[0-9]%',REVERSE(@LIST))) 
                                    END))
    END
    INSERT INTO @RTNVALUE (WORDS)
    SELECT VALUE = LTRIM(RTRIM(@LIST))
    RETURN
END

在第三步中，我使用交叉应用于调用函数，因为该函数针对给定的字符串值返回表格。在选择查询中，我将所有列插入临时表中，以便在下一步中按要求对值进行排序。
SELECT  T.TITLE, A.ID, A.NUMBERS, A.WORDS INTO #FINAL
FROM    #TVSEASON T
        CROSS APPLY dbo.SplitAlphaNumeric(TITLE) A

从临时表#Final中，我使用stuff将所有单词拼接起来，重新生成不包含任何数字的标题，并使用这些值来对标题进行排序。

您可以更改该查询，以按任何顺序排序，例如，如果您想针对文本排序，则首先按textval列排序，然后是数字；但是，如果您想针对标题中使用的所有数字之和进行排序，请按数字而不是文本排序，如我所做的那样，否则，如果您想按简单数字排序而没有求和，则不要使用group by子句和子查询并直接按数字排序。简而言之，在修改下面的查询之后，您可以实现所有与字母数字值相关的序列，并且上面的两个查询是所有目标的基本查询。

SELECT  A.TITLE--, A.NUMBERS, A.TEXTVAL
FROM    (
            SELECT  A.TITLE, 
                    STUFF((
                        SELECT  ' ' + B.WORDS 
                        FROM    #FINAL B
                        WHERE   B.TITLE = A.TITLE
                        FOR XML PATH(''),TYPE).VALUE('(./TEXT())[1]','VARCHAR(MAX)')
                    ,1,1,'') TEXTVAL,
                    SUM(ISNULL(A.NUMBERS,0)) NUMBERS
            FROM    #FINAL A
            GROUP BY A.TITLE
        ) A 
ORDER BY A.TEXTVAL, A.NUMBERS

DROP TABLE #FINAL
DROP TABLE #TVSEASON

在最后，我从内存中删除了两个临时表。我认为这是你想要的排序值的查询，因为如果有人对字母数字值有不同的顺序要求，他们可以在稍微修改一下该查询后实现他们的要求。
2
个人而言，我会尽量避免在SQL中进行复杂的字符串操作。我可能会将其导出到文本文件中，并在类似C＃或Python的语言中使用正则表达式进行处理。然后将其写回到数据库中的单独列中。SQL在字符串操作方面声名狼藉。
然而，这是我对SQL方法的尝试。基本思路是首先消除任何不包含字符串“Season [number]”的行。这处理了没有要解析的季节的情况。我选择将它们与null一起包含，但您也可以在where子句中轻松省略它们，或者给它们一些默认值。我使用“stuff()”函数截断到字符串“Season [number]”之前的所有内容，以便更容易处理。
现在我们有以季节编号开头、可能以一些垃圾结尾的字符串。我使用一个case语句来查看是否存在垃圾（任何非数字字符），如果有，就取最左边的数字字符并丢弃其余部分。如果一开始只有数字，我就保持原样。
最后，将其转换为int，并按此排序。
if object_id('tempdb.dbo.#titles') is not null drop table #titles
create table #titles (Title varchar(100))
insert into #titles (TItle)
select title = '100 Season 1'
union all select '100 Season 2'
union all select '100 Season 03'
union all select '100 Season 4'
union all select '100 Season 10'
union all select 'Another 10th Anniversary Season 01'
union all select 'Another 1st Anniversary Season 01'
union all select 'Another 2nd Anniversary Season 01'
union all select 'Another Season 01'
union all select 'Another Season 02'
union all select 'Show (2008) Season 1'
union all select 'Show (2008) Season 2'
union all select 'Show 2 The 75th Anniversary Season 1'
union all select 'Show Season 1 (2008)'
union all select 'Show Season 2 (2008)'
union all select 'Show Season 10 (2008)'
union all select 'Some Show Another No Season Number'
union all select 'Some Show No Season Number'
union all select 'Some Show With Season Number 1'
union all select 'Some Show With Season Number 2'
union all select 'Some Show With Season Number 10'

;with src as
(
    select 
        Title, 
        Trimmed = case when Title like '%Season [0-9]%' 
                       then stuff(title, 1, patindex('%season [0-9]%', title) + 6, '')
                       else null
                  end
    from #titles
)
select 
    Season = cast(case when Trimmed like '%[^0-9]%' then left(Trimmed, patindex('%[^0-9]%', Trimmed))
         else Trimmed
    end as int),
    Title
from src
order by Season
我喜欢使用 $season [0-9] 早期剥离季节编号的方法。但是，您是否还需要存储已剥离的其余字符串（我们称之为季节名称），并按季节名称和季节#排序？如果仅按季节#排序，则忽略其前面的文本。 - Kairan
还有，我第一次看到这个语法：“;with src as()”，你能解释一下吗？这是某种临时表吗？ - Kairan
1
当然。这是一个通用表达式（也称为CTE）。在这种情况下，它的工作方式与派生表完全相同。关键区别在于它必须在实际语句的开始之前声明。因此，例如，这两个语句是相同的。第一个使用可能更熟悉的派生表，第二个使用CTE。 select * from (select num = 1) a;with someCTE as (select num = 1) select * from someCTE。我有时候更喜欢使用CTE而不是派生表来方便阅读；但它们做的事情是一样的。 - Xedni
0
我的答案利用 OPEN_JSON 将每个标题拆分成单词，然后用相同数量的 'a' 替换数字。例如，2 变成 aa，10 变成 aaaaaaaa。这样就留下了一组行，每行一个单词。然后我使用 STRING_AGG 在每个标题中将它们重新连接起来，创建一个新标题，其中包含用 a 替换的数字。然后按此排序并报告原始标题：
with Words1 as 
(
    select title, REPLACE(REPLACE(value, '(', ''), ')', '') word, [key] as RowN
    from tvseason
   CROSS APPLY OPENJSON('["' +  
      REPLACE(REPLACE(REPLACE(title,' ','","'),'\','\\"'),'"','\"') + 
      '"]')
),
Words2
AS
(
    SELECT title,
           CASE 
                WHEN ISNUMERIC(word) = 1 THEN Replicate('a', CAST(Word as INT))
                WHEN word like '%st' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
                   THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
                WHEN word like '%nd' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
                   THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
                WHEN word like '%rd' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
                   THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
                WHEN word like '%th' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
                   THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
                else Word 
                END As Word,
                rowN
    from words1
),
Words3
AS
(
    SELECT title, STRING_AGG(Word, ' ') WITHIN GROUP (Order By rowN ASC) AS TitleLong
    FROM Words2
    GROUP BY Title
)
SELECT title
FROM Words3
ORDER BY TitleLong

这将会得到以下结果：

**title**
100 Season 1
100 Season 2
100 Season 03
100 Season 4
100 Season 10
Another 1st Anniversary Season 01
Another 2nd Anniversary Season 01
Another 10th Anniversary Season 01
Another Season 01
Another Season 02
Show 2 Season 1
Show Season 1 (2008)
Show Season 2 (2008)
Show Season 10 (2008)
Some Show Another No Season Number
Some Show No Season Number
Some Show With Season Number 1
Some Show With Season Number 2
Some Show With Season Number 10
string_split被具体记录，不保留块的顺序。这将严重影响排序。 - GSerg
看看Brent Ozar关于返回行号的博客 https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/ - Steve Ford
那篇博客文章中的代码将随机顺序号分配给随机块。只有当string_split恰好按字符串顺序返回块时，它才能正常工作。我相信这篇博客文章是在文档中出现明确通知之前编写的，即实际上不能保证顺序。 - GSerg
@GSerg 我已经添加了一个使用 OPENJSON 而不是 STRING_SPLIT 的版本。 - Steve Ford
现在，如果字符串中包含引号 " 或任何具有文字意义的字符序列（例如 \n），将会出现 JSON 格式错误。JSON 将把这些特殊字符转换为它们在 JSON 中表示的对应字符，例如将换行符转换为 \n。 - GSerg
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接