sql - 在 postgresql 中创建一个父项或一组父项的所有子项的表

Question

我有一个父子关系表，我想生成一个表，其中包含一个或多个给定 parent 的所有 child 。

顺便说一句，我不知道如何使用堆栈溢出的 Markdown 创建表，我尝试过但失败了。

示例 1:

表名:关系

Child_ID  Parent_ID  
 1         2  
 2         3  
 3         4  
 4         5  
 5         6  
 6         7  
 7         8  
 8         9  
 9        10  
10        11  

因此对于 Parent_ID 7，结果表应该是:

Child_ID  Parent_ID  
 1         7  
 2         7  
 3         7  
 4         7  
 5         7  
 6         7  

或者，也许使用表格，选择所有 child :

加入如下 parent 表:

Parent_ID  
7  
9  

产生这样的结果:

Child_ID  Parent_ID  
1         7  
2         7  
3         7  
4         7  
5         7  
6         7  
1         9  
2         9  
3         9  
4         9  
5         9  
6         9  
7         9  
8         9  

...当然，如果我想要唯一的 Child_ID，我可以运行不同的。

我想提供一个想法，但我不知道从哪里开始，而且我到处都在寻找这个。感谢您的帮助。

Answer 1

您可以给我们一个RECURSIVE CTE。使用数组函数来创建关系列表。

创建数据

SELECT child_id, parent_id
INTO temp table relations
FROM (VALUES  (1,  2),  
 (2,  3),  
 (3,  4),  
 (4,  5),  
 (5,  6),  
 (6,  7),  
 (7,  8),  
 (8,  9),  
 (9, 10),  
(10, 11) ) as t(child_id, parent_id)

接下来是递归 CTE，我们将从上表中的初始关系开始，然后通过将子项连接到父项来继续添加行(使用 UNION):

WITH recursive children(child_id, parent_id) AS(
SELECT child_id, parent_id
FROM relations
UNION
SELECT relations.child_id, children.parent_id
FROM children
INNER JOIN relations ON relations.parent_id = children.child_id
-- SELECT chain_.parent_id, child_id || children
-- FROM chain_
-- INNER JOIN relations ON relations.parent_id = children[1]
)
SELECT * FROM children where parent_id = 7

产生结果:

child_id | parent_id
---------+----------
     6   |     7
     5   |     7
     4   |     7
     3   |     7
     2   |     7
     1   |     7