Postgres。如何让所有符合孩子标准的父母?
问题描述:
我有点疯狂寻找这个问题的解决方案:Postgres。如何让所有符合孩子标准的父母?
我有这样的表:
表数据
而且我要查询来获取所有通过条件的元素和所有父母,我的意思是,这个结果:
查询结果
我一直在思考上查询:
SELECT a.* FROM table a
JOIN table b ON b.id = a.id
WHERE a.id IN (SELECT DISTINCT c.parent_id FROM table c WHERE c.condition = TRUE)
OR b.id IN (SELECT DISTINCT c.id FROM table c WHERE c.condition = TRUE);
但我只能得到差一个级别用这种方法,我的意思是,我不能没有获得超过1个父条件。 非常感谢。
答
您可以使用此一recursive CTE:
WITH RECURSIVE recCTE AS
(
/*Get all the true children to seed the recursive query*/
SELECT
id,
parent_id,
condition as initial_condition,
1 as depth,
CAST(id as varchar(50)) as path
FROM
table a
WHERE
a.id NOT IN (SELECT DISTINCT parent_id from table)
and a.condition = 'true'
UNION ALL
/*Recursive bit that refers back to itself. Find the parents*/
SELECT
b.id,
b.parent_id,
a.initial_condition,
depth + 1 as depth,
cast(path || '>' || b.id as varchar(50)) as path
FROM
recCTE a
INNER JOIN table b ON
a.parent_id = b.id
WHERE
/*avoid going too deep in case of cycling*/
depth <= 20
)
SELECT * FROM recCTE
递归CTE使用两个部分:
的递归种子:这是UNION查询的前半部分。在这里,我们确定所有的孩子(ID不是父母ID)是“真”
递归术语:这是UNION查询的后半部分。它在FROM子句中引用自身(recCTE),并再次加入
table
;将recCTE.parent_id(之前的迭代parent_id)链接到表id
。然后提取该迭代所需的所有信息。
我几乎总是跟踪递归深度(也有多少递归才能让这个记录),以及路径(从最底层的孩子开始此层次的其它节点没有我们打去这个记录)。
我使用深度来确保我们不会在兔子洞下太远。倘若你有一个像记录:
+----+-----------+
| id | parent_id |
+----+-----------+
| 1 | 5 |
| 5 | 7 |
| 7 | 1 |
+----+-----------+
这将导致无限循环(循环)最坏的情况是不言而喻20次深后停止(1> 5> 7> 1> 5> 7> 1> 5> 7> 1> 5> 7> 1> 5> 7> 1> 5> 7> 1> 5)。还有其他方法可以停止骑车,例如使用路径字段:WHERE a.path NOT LIKE '%' || a.parent_id || '%'
。
如果需要,您可以选择最终选择,但这会让您获得95%的选择。
哇,真的很好的解释,我变得疯狂了,我读了一点关于递归CTE,并惊讶! 谢谢你完全保存了一天。我调整了查询到我的表,并罚款:D –
太棒了!我很高兴它开箱即用。递推CTE的确有一条学习曲线,但是一旦你把头靠近它们,它们对于工具箱来说是非常有意义的,也是一个很好的工具。 – JNevill