sql - 如何使用 SQL Query 对表记录进行排序，父项后跟子项

Question

我有一个表，其中很少有记录，其父记录如下所示。并非所有人都有父母记录。

id | parent_id
--------------
1 |  0
2 |  0
3 |  1
4 |  0
5 |  0
6 |  0
7 |  5

我希望以父母后跟孩子的方式订购记录：

id | parent_id
--------------
1 |  0
3 |  1
2 |  0
4 |  0
5 |  0
7 |  5
6 |  0

如何在不使用存储过程的情况下使用 SQL 查询来实现这一点？

我正在使用postgres。

标签： sqlpostgresqlhierarchical-data

Answer 1

假设像您的示例所暗示的那样，可能只有一层层次结构：

SELECT child.*
FROM   tbl      AS child
LEFT   JOIN tbl AS parent ON parent.id = child.parent_id
ORDER  BY COALESCE(parent.id, child.id)  -- order by parent if exists
        , parent.id IS NOT NULL          -- parent first per group
        , child.id;                      -- order rest by id

仅当我们按“名称”之类的附加属性进行排序时才需要连接（这是典型的情况，因为代理 ID 的值没有意义）。虽然仅像您演示的那样按 ID 排序，但我们不需要连接，因为所有信息都已经存在（就像 Gordon 也演示的那样）。然后我们可以简化：

SELECT *
FROM   tbl
ORDER  BY CASE WHEN parent_id = 0 THEN id ELSE parent_id END
        , parent_id <> 0
        , id;

• 第二ORDER BY项需要在其子项之前对父项进行排序。工作，因为FALSE排序之前TRUE。看：
  • 将空值排在所有其他值之后，特殊除外
• ORDER BY只有当可以有多个孩子时才需要最后一项。

db<>fiddle here - 使用扩展测试用例来证明ORDER BY项目的相关性。