Oracle数据库到Postgesql层次查询的迁移

9w11ddsr 于 2023-03-17 发布在 Oracle

关注(0)|答案(1)|浏览(97)

我正在做的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 bt 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_)

Postgres查询：

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)`

此代码工作，但不正确

oracle

来源：https://stackoverflow.com/questions/75729456/migration-fron-oracle-db-to-postgesql-hierarchical-queries

1条答案

按热度按时间

jhdbpxl91#

您没有在Oracle查询中使用LEFT OUTER JOIN。是的，您可以在联接中使用(+)，但在START WITH和CONNECT BY子句中，您要求spl.index和spl.id等于另一个值，当出现OUTER JOIN时，这将永远不会成立，值将为NULL，因此联接只能为INNER JOIN。
因此，您的Oracle查询可以有效地：

SELECT COUNT(*) cnt
FROM   list l
       INNER JOIN parts p
       ON ( l.id = p.id )
WHERE  l.month     = Month_
AND    l.year      = Year_
AND    l.date_del  IS NULL
AND    l.dep_owner = dep_owner_
AND    l.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 p.id = p.parent_id
START WITH p.index = trim(part_index_)

至少，您需要将LEFT JOIN s更改为INNER JOIN s。
也不要将主递归子查询因子分解子句（CTE）联接到其自身以使其递归。
您需要类似以下内容（未测试，因为您尚未共享任何示例数据）：

WITH RECURSIVE depts AS (
  SELECT "DEP_ID"
  FROM   departments
  WHERE  "DEP_ID"= dep_id_
UNION ALL
  SELECT d."DEP_ID"
  FROM   departments d
         INNER JOIN depts p ON d."PARENT_ID" = p."DEP_ID"
),
recursive_parts AS (
  SELECT p."ID" 
  FROM   list l
         INNER JOIN parts p
         ON p."ID" = l."ID"
  WHERE  l."MONTH"     = Month_
  AND    l."YEAR"      = Year_
  AND    l."DATE_DEL"  IS NULL
  AND    l."DEP_OWNER" = dep_owner_
  AND    l."DEP_ID"    IN ( SELECT "DEP_ID" FROM depts )
  AND    p."INDEX" = TRIM(part_index_)
UNION ALL
  SELECT p."ID"
  FROM   list l
         INNER JOIN parts p
         ON p."ID" = l."ID"
         INNER JOIN recursive_parts r
         ON r."ID" = p."PARENT_ID"
  WHERE  l."MONTH"     = Month_
  AND    l."YEAR"      = Year_
  AND    l."DATE_DEL"  IS NULL
  AND    l."DEP_OWNER" = dep_owner_
  AND    l."DEP_ID"    IN ( SELECT "DEP_ID" FROM depts )
)
SELECT count(*) cntv
FROM   recursive_parts

赞(0）回复(0）举报 2023-03-17

我来回答

Oracle数据库到Postgesql层次查询的迁移

1条答案

相关问题

热门标签

最新问答