-- pg 树形查询,根据父id,查找子id
WITH RECURSIVE organization_tree AS (
SELECT id, name, parent_id,company_id, 1 as level
FROM hr_department
WHERE company_id IS not NULL
UNION ALL
SELECT o.id, o.name, o.parent_id,o.company_id, ot.level + 1
FROM hr_department o
JOIN organization_tree ot ON o.parent_id = ot.id
)
SELECT id, name, parent_id,company_id, level
FROM organization_tree
ORDER BY level, id;
-- pg 树形查询,根据子id,查找所有的父id
WITH RECURSIVE organization_ancestors AS (
SELECT id, parent_id,name,company_id
FROM hr_department
WHERE id = 3421 -- 替换为要查找的子节点的ID
UNION ALL
SELECT o.id, o.parent_id,o.name,o.company_id
FROM hr_department o
JOIN organization_ancestors oa ON o.id = oa.parent_id
)
SELECT company_id
FROM organization_ancestors where company_id is not null;
pg树形查询
最新推荐文章于 2024-07-20 20:04:43 发布