Postgres自连接递归CTE祖先链

Question

我有一个代表直接祖先的pilates_bill表(不是树结构)。

bill_id (pk) | previous_bill_id (self-join fk)
=============+================================
1               2
2               3
3               4
5               NULL

需要为任何给定行生成所有祖先的列表(父辈/祖父母/其他)(下面的示例以1开头)。

使用递归CTE获取具有祖先链的bill_ids列表

WITH RECURSIVE chain(from_id, to_id) AS (
  SELECT NULL::integer, 1  -- starting id
  UNION
  SELECT c.to_id, pilates_bill.previous_bill_id
  FROM chain c
  LEFT OUTER JOIN pilates_bill ON (pilates_bill.bill_id = to_id)
  WHERE c.to_id IS NOT NULL
)
SELECT from_id FROM chain WHERE from_id IS NOT NULL;

预期结果1、2、3、4、5

但现在，当我试图按祖先顺序生成表行时，结果就坏了。

SELECT * FROM pilates_bill WHERE bill_id IN
(
WITH RECURSIVE chain(from_id, to_id) AS (
  SELECT NULL::integer, 1
  UNION
  SELECT c.to_id, pilates_bill.previous_bill_id
  FROM chain c
  LEFT OUTER JOIN pilates_bill ON (pilates_bill.bill_id = to_id)
  WHERE c.to_id IS NOT NULL
)
SELECT from_id FROM chain WHERE from_id IS NOT NULL
)

行顺序为5,1,2,3,4

我在这里做错了什么？

Answer 1

除非您指定了order by，否则SQL查询返回的行是随机排列的。

您可以通过在递归CTE中跟踪它来计算深度：

WITH RECURSIVE chain(from_id, to_id, depth) AS
    (
    SELECT  NULL::integer
    ,       1
    ,       1
    UNION
    SELECT  c.to_id
    ,       pb.previous_bill_id
    ,       depth + 1
    FROM chain c
    LEFT JOIN 
            pilates_bill pb
    ON      pb.bill_id = c.to_id
    WHERE   c.to_id IS NOT NULL
    )
SELECT  * 
FROM    chain
ORDER BY
        depth