sql - SQL，如何对具有优先约束的查询结果进行排序

Question

我有两个关系为 1-n 的表。我正在寻找订购元素的方法。我可以使用列“position(int)”，但我更喜欢使用优先级的 id：

+-------+---------------+-------+
| pk_id | precedence_id | fk_id |
+-------+---------------+-------+
|     4 |             1 |    10 |
+-------+---------------+-------+
|     1 |             6 |    10 |
+-------+---------------+-------+
|     2 |             5 |    40 |
+-------+---------------+-------+
|     3 |          NULL |    10 |
+-------+---------------+-------+
|     6 |             3 |    10 |
+-------+---------------+-------+
|     5 |          NULL |    40 |
+-------+---------------+-------+

我在同一张表上有一个主键（pk_id）、一个外键（fk_id）和一个优先约束（precedence_id）。

我正在寻找查询以获取具有优先级的结果：

+-------+---------------+-------+
| pk_id | precedence_id | fk_id |
+-------+---------------+-------+
|     3 |          NULL |    10 |
+-------+---------------+-------+
|     6 |             3 |    10 |
+-------+---------------+-------+
|     1 |             6 |    10 |
+-------+---------------+-------+
|     4 |             1 |    10 |
+-------+---------------+-------+
|     5 |          NULL |    40 |
+-------+---------------+-------+
|     2 |             5 |    40 |
+-------+---------------+-------+

SELECT * 
FROM tb 
ORDER BY fk_id, ??

标签： sqlpostgresql

Answer 1

这工作正常：

WITH RECURSIVE recursive(pk_id, precedence_id, position) AS (
    SELECT pk_id, precedence_id, 0
    FROM tb
    WHERE precedence_id ISNULL

    UNION ALL

    SELECT v.pk_id, v.precedence_id, rec.position + 1
    FROM
        tb v
        INNER JOIN recursive rec ON rec.pk_id = v.precedence_id
)
SELECT tst.*, rec.position
FROM
    recursive rec
    INNER JOIN tb tst ON rec.pk_id = tst.pk_id
ORDER BY tst.fk_id, rec.position;