我想在Postgres中的单个表上创建一个RECURSIVE查询，它基本上是基于Parent和child的。
这是演示表带有数据的员工
id parentid managerid status
------------------------------------
3741 [null] 1709 7
3742 3741 1709 12
3749 3742 1709 12
3900 3749 1709 4
1( 如果状态=12，则结果为，状态为的数据和该特定节点的所有父节点。
预期结果是：
id parentid managerid status
--------------------------------------
3741 [null] 1709 7
3742 3741 1709 12
3749 3742 1709 12
为此，我尝试了下面给出的查询，即使我更改了状态值，它也能正常工作，并给出正确的结果。
WITH RECURSIVE nodes AS (
SELECT s1.id, case when s1.parentid=s1.id then null else s1.parentid end parentid,s1.managerid, s1.status
FROM employees s1 WHERE id IN
(SELECT employees.id FROM employees WHERE
"employees"."status" = 12 AND "employees"."managerid" = 1709)
UNION ALL
SELECT s2.id, case when s2.parentid=s2.id then null else s2.parentid end parentid,s2.managerid, s2.status
FROM employees s2 JOIN nodes ON s2.id = nodes.parentid
)
SELECT distinct nodes.id, nodes.parentid, nodes.managerid, nodes.status
FROM nodes ORDER BY nodes.id ASC NULLS FIRST;
2( 如果状态！=12，则结果将是，只有该特定节点的所有父节点。
预期结果是：
id parentid managerid status
--------------------------------------
3741 [null] 1709 7
我希望状态查询不等于某个值。
WITH RECURSIVE cte AS (
SELECT * FROM tablename
WHERE status != 12
UNION
SELECT t.*
FROM tablename t INNER JOIN cte c
ON c.parentid = t.id
)
SELECT DISTINCT * FROM cte;
有关更多信息，请参阅演示：演示
这是一个非常简单的解决方案，但我认为它应该适用于较小的数据集
SELECT * FROM employee
WHERE
status=12
OR id IN (
SELECT DISTINCT parentId FROM employee WHERE status=12
)
`
使用此递归CTE:
with recursive cte as (
select * from tablename
where status = 12
union all
select t.*
from tablename t inner join cte c
on c.parentid = t.id
)
select distinct * from cte;
请参阅演示
结果：
| id | parentid | managerid | status |
| ---- | -------- | --------- | ------ |
| 3741 | | 1709 | 7 |
| 3742 | 3741 | 1709 | 12 |
| 3749 | 3742 | 1709 | 12 |
WITH RECURSIVE CTE AS
(
SELECT *
FROM tablename
WHERE status = 12
UNION
SELECT t.*
FROM tablename t
INNER JOIN cte c ON c.Id = t.parentid
)
SELECT t.*
FROM tablename t
LEFT JOIN cte c on t.id=c.id
WHERE c.id IS NULL
ORDER BY id ASC NULLS FIRST;