使用单一查询选择所有祖先

Question

我在PostgreSQL中有下面的表格，需要获得所有具有给定ID的人的祖先。

还必须能够区分父亲和母亲的结果。

Person table - has about 1M rows, schema looks like this:
+-----+--------+--------+
| id  | father | mother |
+-----+--------+--------+
|   1 |      2 |      3 |
|   2 |      4 |      5 |
|   3 |      6 |      7 |
|   4 |      8 |      9 |
|   5 |     10 |     11 |
| ... |    ... |    ... |
| ... |    ... |    ... |
+-----+--------+--------+

目前，我在循环中执行查询，每个人获得一行。

是否可以在单个查询(或2个查询)中获取所有祖先？

查询id 2的示例结果：

+----+--------+--------+
| id | father | mother |
+----+--------+--------+
|  2 |      4 |      5 |
|  4 |      8 |      9 |
|  5 |     10 |     11 |
+----+--------+--------+

Answer 1

WITH recursive ParentOf (id, father, mother )
AS
(
-- Anchor query
    SELECT id, father, mother 
    FROM test
    WHERE id = ? -- e.g. 2
    UNION ALL
-- Recursive query
    SELECT t.id, t.father, t.mother
    FROM test t
    INNER JOIN ParentOf
    ON t.id = ParentOf.father OR t.id = ParentOf.mother
)
-- Statement that executes the CTE
SELECT id, father, mother 
FROM ParentOf;

Answer 2

这回答了问题的原稿。

为此，最简单的方法是用每个父级的一列来取消表的枢轴。然后使用递归的CTE来获取所有的父级。

代码如下所示：

with recursive t as (
      select 1 as id, 2 as father, 3 as mother union all
      select 2, 4, 5 union all
      select 3, 6, 7 union all
      select 4, 8, 9 union all
      select 5, 10, 11
     ),
     parents as (
      select id, father as parent from t union all
      select id, mother from t
     ),
     cte as (
      select p.id, p.parent
      from parents p
      where id = 2  -- or whatever id you want
      union all
      select cte.id, p.parent
      from cte join
           parents p
           on cte.parent = p.id
     )
select *
from cte;

这里是一个SQL。

Answer 3

如果我猜对了，你只需使用JOIN或简单的IN()

对于IN来说，它应该是这样的：

SELECT 
    p.id, 
    p.father,
    p.mother 
FROM person p
WHERE 
p.id = 2
OR p.id IN (SELECT father FROM person WHERE id = 2)
OR p.id IN (SELECT mother FROM person WHERE id = 2)

这会让你：

| id | father | mother |
|----|--------|--------|
|  2 |      4 |      5 |
|  4 |      8 |      9 |
|  5 |     10 |     11 |

对于JOIN，您可以在列上进行自连接，以便将其与id配对。就像这样：

SELECT 
      x.id as id, 
      x.father as father, 
      x.mother as mother
FROM person p
LEFT JOIN person x ON x.id = p.id OR x.id = p.father OR x.id = p.mother
WHERE 
  p.id = 2

这也会给你同样的结果。

从这里开始，您可以在WHERE子句下使用更多的条件来获得适当的结果。

小提琴演示