如何在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屋！