Postgres - 如何通过 UNION ALL 实现 UNION 的行为？

Question

我有一张带有父级（parent）和子级（child）编号的表格。

create table if not exists stack (
    parent int,
    child int
)

每个父节点可以有多个子节点，每个子节点又可以再拥有多个子节点。

insert into stack (parent, child) values
    (1,2),
    (2,3),
    (3,4),
    (4,5),
    (5,6),
    (6,7),
    (7,8),
    (8,9),
    (9,null),
    (1,7),
    (7,8),
    (8,9),
    (9,null);

数据长这个样子。

|parent|child|
|------|-----|
|1     |2    |
|2     |3    |
|3     |4    |
|4     |5    |
|5     |6    |
|6     |7    |
|7     |8    |
|8     |9    |
|9     |NULL |
|1     |7    |
|7     |8    |
|8     |9    |
|9     |NULL |

我想找到所有子元素。我可以使用带有UNION ALL的递归CTE。

with recursive cte as (
select
    child
from
    stack
where
      stack.parent = 1
union
select
    stack.child
from
    cte
left join stack on
    cte.child = stack.parent
where
    cte.child is not null
  )
select * from cte;

这让我得到了我想要的结果。

|child|
|-----|
|2    |
|7    |
|3    |
|8    |
|4    |
|9    |
|5    |
|NULL |
|6    |

然而，我想要包括每个节点的深度/级别和路径信息。我可以使用另一个递归CTE来实现这一点。

with recursive cte as (
select
    parent,
    child,
    0 as level,
    array[parent,
    child] as path
from
    stack
where
      stack.parent = 1
union all
select
    stack.parent,
    stack.child,
    cte.level + 1,
    cte.path || stack.child
from
    cte
left join stack on
    cte.child = stack.parent
where
    cte.child is not null
  )
select * from cte;

这给我提供了这些数据。

|parent|child|level|path                |
|------|-----|-----|--------------------|
|1     |2    |0    |{1,2}               |
|1     |7    |0    |{1,7}               |
|2     |3    |1    |{1,2,3}             |
|7     |8    |1    |{1,7,8}             |
|7     |8    |1    |{1,7,8}             |
|3     |4    |2    |{1,2,3,4}           |
|8     |9    |2    |{1,7,8,9}           |
|8     |9    |2    |{1,7,8,9}           |
|8     |9    |2    |{1,7,8,9}           |
|8     |9    |2    |{1,7,8,9}           |
|4     |5    |3    |{1,2,3,4,5}         |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|9     |     |3    |{1,7,8,9,}          |
|5     |6    |4    |{1,2,3,4,5,6}       |
|6     |7    |5    |{1,2,3,4,5,6,7}     |
|7     |8    |6    |{1,2,3,4,5,6,7,8}   |
|7     |8    |6    |{1,2,3,4,5,6,7,8}   |
|8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
|8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
|8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
|8     |9    |7    |{1,2,3,4,5,6,7,8,9} |
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|
|9     |     |8    |{1,2,3,4,5,6,7,8,9,}|

我的问题是我有很多重复的数据。我想得到与UNION查询相同的结果，但是还带有级别和路径。

我尝试了一些类似于：

where
    cte.child is not null
    and stack.parent not in (cte.parent)

或者

where
    cte.child is not null
    and not exists (select parent from cte where cte.parent = stack.parent)

但第一个不会改变任何东西，而第二个会返回一个错误。

ERROR: recursive reference to query "cte" must not appear within a subquery

有什么想法吗？非常感谢！

- zemirco

---

你无法得到相同的结果，因为存在多条路径，例如对于1..9。你需要决定你喜欢哪条路径，最长的、最短的或其他标准。 - Serg

Answer 1

您的问题是表格数据不当。例如，您的表格中包含了8是7的直接子节点两次的信息。我建议您删除重复的数据，并在这些对中实施唯一性约束。

如果由于某种原因您无法这样做，请确保在查询中使行不同：

with recursive
  good_stack as (select distinct * from stack)
 ,cte as 
  (
    select
      parent,
      child,
      0 as level,
      array[parent,
      child] as path
    from good_stack
    where good_stack.parent = 1
    union all
    select
      good_stack.parent,
      good_stack.child,
      cte.level + 1,
      cte.path || good_stack.child
    from cte
    left join good_stack on cte.child = good_stack.parent
    where cte.child is not null and good_stack.child is not null
  )
select * from cte;

演示: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=acb1d7a1a1d26c3fd9caf0e7dedc12b2

（您还可以将列设置为非空。条目9|null不添加任何信息。如果表中缺少这些条目，则9仍然没有子代。）

- Thorsten Kettner