在PostgreSQL中获取行的序列（链表）

Question

我有一个名为submissions的表格，它本质上是一个单向链表。给定某一行的id，我想返回该行所在的整个链表（并按正确顺序排列）。例如，在下面的表格中，如果有id 2，我希望以1,2,3,4的顺序返回相应的行。

(4,3) -> (3,2) -> (2,1) -> (1,null)

我期望在这里看到1,2,3,4，因为4本质上是列表的头部，而2属于该列表，我想遍历整个列表。http://sqlfiddle.com/#!15/c352e/1有没有一种使用postgresql的递归CTE方法来实现这一点？ 到目前为止，我只能得到父级，而不能得到子孙。

WITH RECURSIVE "sequence" AS (
  SELECT * FROM submissions WHERE "submissions"."id" = 2
  UNION ALL SELECT "recursive".* FROM "submissions" "recursive"
    INNER JOIN "sequence" ON "recursive"."id" = "sequence"."link_id"
)

SELECT "sequence"."id" FROM "sequence"

- Kyle Decot

---

只是想知道，你能详细解释一下如何从数据中的 2 获取 1,2,3,4 吗？如果我们从 id=2 开始：(2,1) -> (1,null)... 看起来你的查询是正确的，但期望结果是错误的。 - undefined

@Abelisto 我需要能够提供序列中的任何 id。因此，如果我提供了1、2、3或4，我应该得到相同的结果，因为我基本上想要整个行的血统。 - undefined

Answer 1

这种方法利用了您已经想出的内容。它添加了另一个块来计算列表的剩余部分，然后将两者结合在一起进行自定义反向排序。

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 submissions WHERE submissions.id = 2
  UNION ALL
  SELECT pathtobottom.i + 1 i, -- add fake order column to reverse retrieved records
  recursive.* FROM submissions recursive
    INNER JOIN pathtobottom ON recursive.id = pathtobottom.link_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 submissions WHERE submissions.id = 2
  UNION ALL
  SELECT pathtotop.i + 1 i, -- add fake order column to reverse retrieved records
  recursive2.* FROM submissions recursive2
    INNER JOIN pathtotop ON recursive2.link_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 != 2 -- remove element to avoid duplicate
UNION ALL
SELECT pathtotop.id FROM pathtotop;
/*
-- Elements ordered from top to bottom
SELECT pathtotoprev.id FROM pathtotoprev
UNION ALL
SELECT pathtobottom.id FROM pathtobottom where id != 2; -- remove element to avoid duplicate
*/

- cachique

Answer 2

这是我大脑的又一次探索。谢谢。

with recursive r as (
  select *, array[id] as lst from submissions s where id = 6
  union all 
  select s.*, r.lst || s.id 
  from 
    submissions s inner join 
      r on (s.link_id=r.id or s.id=r.link_id)
    where (not array[s.id] <@ r.lst)
)
select * from r;

- Abelisto