如何在sql中对链表进行排序？

我已将链表实现为自引用数据库表:

CREATE TABLE LinkedList(
    Id bigint NOT NULL,
    ParentId bigint NULL,
    SomeData nvarchar(50) NOT NULL) 

其中Id是主键,ParentId是列表中上一个节点的Id.第一个节点有ParentId = NULL.

我现在想要从表中进行SELECT,按照它们应该出现的顺序对行进行排序,作为列表中的节点.

例如:如果表包含行

Id      ParentId  SomeData
24971   NULL      0
38324   24971     1
60088   60089     3
60089   38324     2
61039   61497     5
61497   60088     4
109397  109831    7
109831  61039     6

然后使用标准对其进行排序,结果应该是:

Id      ParentId  SomeData
24971   NULL      0
38324   24971     1
60089   38324     2
60088   60089     3
61497   60088     4
61039   61497     5
109831  61039     6
109397  109831    7

你应该使用SomeData colum作为控件,所以请不要作为SomeData的ORDER作弊 :-)

我找到了一个SQLServer的解决方案,但看起来比Quassnoi更大,更不优雅

WITH SortedList (Id, ParentId, SomeData, Level)
AS
(
  SELECT Id, ParentId, SomeData, 0 as Level
    FROM LinkedList
   WHERE ParentId IS NULL
  UNION ALL
  SELECT ll.Id, ll.ParentId, ll.SomeData, Level+1 as Level
    FROM LinkedList ll
   INNER JOIN SortedList as s
      ON ll.ParentId = s.Id
)

SELECT Id, ParentId, SomeData
  FROM SortedList
 ORDER BY Level

在Oracle中:

SELECT Id, ParentId, SomeData
FROM (
  SELECT ll.*, level AS lvl
  FROM LinkedList ll
  START WITH
    ParentID IS NULL
  CONNECT BY
    ParentId = PRIOR Id
)
ORDER BY
  lvl

PS使用NULLas 是一种不好的做法ParentID,因为索引无法搜索.插入id为0或-1代替的代理根,然后使用START WITH ParentID = 0.

(编辑:d哦!我正在调试你发现它!)

在SQL Server中:

;WITH cte (Id, ParentId, SomeData, [Level]) AS (
    SELECT Id, ParentId, SomeData, 0
    FROM LinkedList
    WHERE ParentId IS NULL
    UNION ALL
    SELECT ll.Id, ll.ParentId, ll.SomeData, cte.[Level] + 1
    FROM LinkedList ll
    INNER JOIN cte ON ll.ParentID = cte.ID
)
SELECT * FROM cte
ORDER BY [Level]