如何在SQL Server中按指定链式规则对表行进行排序？

需求是从StateID=1开始，按照NextStateID与下一行StateID匹配的链式规则，依次排列表中数据，直到没有匹配的StateID为止。

测试数据 #

DECLARE @t TABLE
(
    StateID int,
    NextStateID int,
    Description varchar(255)
)

INSERT INTO @t 
VALUES (874, 2, 'A'), 
       (1631, 3, 'B'),
       (935, 344, 'C'),
       (907, 813, 'D'),
       (2, 814, 'E'),
       (813, 874, 'F'),
       (1, 907, 'G'),
       (814, 935, 'H'),
       (344, 1631, 'I')

解决方案：使用递归CTE #

递归CTE是处理这种链式遍历场景的最佳选择，通过锚点定义起始行，递归成员依次关联后续行，同时记录遍历的层级用于排序：

WITH StateChain AS
(
    -- 锚点成员：起始行StateID=1，层级Level=1
    SELECT 
        StateID, 
        NextStateID, 
        Description,
        1 AS Level
    FROM @t
    WHERE StateID = 1

    UNION ALL

    -- 递归成员：关联下一行StateID等于上一行的NextStateID，层级递增
    SELECT 
        t.StateID, 
        t.NextStateID, 
        t.Description,
        sc.Level + 1 AS Level
    FROM @t t
    JOIN StateChain sc ON t.StateID = sc.NextStateID
)
SELECT StateID, NextStateID, Description
FROM StateChain
ORDER BY Level;

执行结果 #

说明 #

• 锚点成员定位到起始行（StateID=1），并初始化层级为1。
• 递归成员通过JOIN关联当前行的NextStateID与下一行的StateID，同时层级加1，实现链式遍历。
• 最终通过Level字段排序，得到符合要求的链式结果。

内容的提问来源于stack exchange，提问作者Ivan