获取postgresql中的行序列(链表)

kg7wmglp  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(382)

我有一张table叫 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来获取从delivery 1开始到delivery 4结束的序列中的所有交付。
预期选择:

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

jmp7cifd1#

我使用这个解决方案是基于我在这里发现的:
在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;

相关问题