在 PostgreSQL 中获取行序列(链接列表)

Question

我有一个名为deliveries的表。其中一些交付是序列的一部分(有父母或孩子或两者兼而有之(，其中一些交付是一次性的。

id    parent_delivery_id    child_delivery_id
---------------------------------------------
1     NULL                  2
2     1                     3
3     2                     4
4     3                     NULL
5     NULL                  NULL
6     NULL                  NULL
7     NULL                  8
8     7                     NULL

使用上面的例子，我想写一些 SQL 来获取从交付 1 开始到交付 4 结束的序列中的所有交付。

预期选择：

id    parent_delivery_id    child_delivery_id
---------------------------------------------
1     NULL                  2
2     1                     3
3     2                     4
4     3                     NULL

Answer 1

我根据在这里找到的内容使用了这个解决方案：

在PostgreSQL中获取行的序列(链接列表(

WITH RECURSIVE pathtobottom AS (
-- Get the path from element to bottom list following next element id that matches current link_id
SELECT 1 i, -- add fake order column to reverse retrieved records
* FROM deliveries WHERE deliveries.id = 1
UNION ALL
SELECT pathtobottom.i + 1 i, -- add fake order column to reverse retrieved records
recursive.* FROM deliveries recursive
INNER JOIN pathtobottom ON recursive.id = pathtobottom.parent_delivery_id
)
,  pathtotop AS (
-- Get the path from element to top list following previous element link_id that matches current id
SELECT 1 i, -- add fake order column to reverse retrieved records
* FROM deliveries WHERE deliveries.id = 1
UNION ALL
SELECT pathtotop.i + 1 i, -- add fake order column to reverse retrieved records
recursive2.* FROM deliveries recursive2
INNER JOIN pathtotop ON recursive2.parent_delivery_id = pathtotop.id
), pathtotoprev as (
-- Reverse path to top using fake 'i' column
SELECT pathtotop.id FROM pathtotop order by i desc
), pathtobottomrev as (
-- Reverse path to bottom using fake 'i' column
SELECT pathtobottom.id FROM pathtobottom order by i desc
)
-- Elements ordered from bottom to top
SELECT pathtobottomrev.id FROM pathtobottomrev where id != 1 -- remove element to avoid duplicate
UNION ALL
SELECT pathtotop.id FROM pathtotop;