Oracle数据库迁移至PostgreSQL：层级查询改写求助

问题背景 #

正在将Oracle数据库迁移至PostgreSQL，已完成简单层级查询的迁移，但以下这条层级查询无法正确改写。原Oracle查询可正常运行，自行尝试的PostgreSQL查询虽能执行但结果不符合预期，请求提供正确的PostgreSQL语句。

原Oracle查询语句 #

select count(*) cnt
from   list nl,
       parts spl
where  nl.month     = Month_
and    nl.year      = Year_
and    nl.date_del  is null
and    nl.id(+)     = spl.id
and    nl.dep_owner = dep_owner_
and    nl.dep_id in ( select d.dep_id
                      from departments d
                      connect by prior d.dep_id = d.parent_id
                      start with d.dep_id = dep_id_)
connect by prior spl.id = spl.parent_id
start with spl.index = trim(part_index_)

尝试的PostgreSQL查询（可运行但结果不正确） #

WITH RECURSIVE cteroot AS (SELECT count(*) AS cnt
                       FROM parts spl
                       LEFT JOIN list nl ON spl."ID" = nl."ID"
                       where nl."MONTH"= Month_
                       and nl."YEAR" = Year_
                       and nl."DATE_DEL" is null
                       and nl."DEP_OWNER" = dep_owner_
                       and nl."DEP_ID" IN (WITH RECURSIVE cte AS (SELECT "DEP_ID"
                                           FROM departments
                                           where "DEP_ID"= dep_id_
                                           UNION
                                           SELECT d."DEP_ID"
                                           FROM departments d
                                           JOIN cte ON d."PARENT_ID"= cte."DEP_ID")
                                           SELECT "DEP_ID"
                                           FROM cte
                                           )
                       and spl."INDEX" = TRIM(part_index_)
                       UNION
                       SELECT count(*) AS cntd
                       FROM parts spld
                       LEFT JOIN list nld ON spl."ID" = nld."ID"
                       where nld."MONTH"= Month_
                       and nld."YEAR" = Year_
                       and nld."DATE_DEL" is null
                       and nld."DEP_OWNER" = dep_owner_
                       and nld."DEP_ID" IN (WITH RECURSIVE cted AS (SELECT "DEP_ID"
                                                                    FROM departments dt
                                                   where dt."DEP_ID"= dep_id_
                                                   UNION
                                                   SELECT td."DEP_ID"
                                                   FROM departments td
                                                   JOIN cted ON td."PARENT_ID"= cted."DEP_ID")
                                             SELECT "DEP_ID"
                                             FROM cted
                                             )
 SELECT count(*) cntv
 FROM cteroot

注：上述PostgreSQL查询可运行但结果不正确

正确的PostgreSQL查询语句 #

WITH RECURSIVE dept_cte AS (
    -- 获取目标部门及其所有子部门的dep_id
    SELECT dep_id
    FROM departments
    WHERE dep_id = dep_id_
    UNION ALL
    SELECT d.dep_id
    FROM departments d
    JOIN dept_cte dc ON d.parent_id = dc.dep_id
),
parts_cte AS (
    -- 获取目标part节点及其所有子节点
    SELECT id
    FROM parts
    WHERE "INDEX" = TRIM(part_index_)
    UNION ALL
    SELECT p.id
    FROM parts p
    JOIN parts_cte pc ON p.parent_id = pc.id
)
SELECT COUNT(*) AS cnt
FROM parts_cte pc
LEFT JOIN list nl ON pc.id = nl.id
WHERE 
    nl.month = Month_
    AND nl.year = Year_
    AND nl.date_del IS NULL
    AND nl.dep_owner = dep_owner_
    AND nl.dep_id IN (SELECT dep_id FROM dept_cte);

逻辑说明 #

• dept_cte：递归遍历departments表，从指定dep_id_开始，获取该部门及其所有子部门的dep_id，与原Oracle子查询的递归逻辑完全对齐
• parts_cte：递归遍历parts表，从trim(part_index_)对应的节点开始，获取该节点及其所有子节点，对应原Oracle中connect by prior spl.id = spl.parent_id的层级遍历逻辑
• 最后将递归得到的parts集合与list表做左连接，应用过滤条件后统计符合要求的记录总数，完全匹配原Oracle查询的业务逻辑

内容的提问来源于stack exchange，提问作者AlexGro