如何在sql中对链表进行排序? [英] How do I sort a linked list in sql?
问题描述
我已经实现了一个链表作为自引用数据库表:
I have implemented a linked list as a self-referencing database table:
CREATE TABLE LinkedList(
Id bigint NOT NULL,
ParentId bigint NULL,
SomeData nvarchar(50) NOT NULL)
其中,Id是主键,ParentId是列表中上一个节点的ID.第一个节点的ParentId = NULL.
where Id is the primary key, and ParentId is the Id of the previous node on the list. The first node has ParentId = NULL.
我现在想从表中进行SELECT,以应将它们作为列表上的节点的出现顺序进行排序.
I now want to SELECT from the table, sorting the rows in the same order they should appear, as nodes on the list.
例如:如果表格包含行
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
然后使用条件对它进行排序,应得出:
Then sorting it, using the criteria, should result in:
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 列作为控件,所以请不要欺骗SomeData的 ORDER :-)
You're supposed to use the SomeData colum as a control, so please don't cheat doing ORDER by SomeData :-)
推荐答案
在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
P. S.将NULL
用作ParentID
是错误的做法,因为它无法通过索引进行搜索.插入ID为0
或-1
的代理根,并使用START WITH ParentID = 0
.
P. S. It's a bad practice to use NULL
as ParentID
, as it is not searchable by indices. Insert a surrogate root with id of 0
or -1
instead, and use START WITH ParentID = 0
.
这篇关于如何在sql中对链表进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!