时间戳

首页 > 解决方案 > 将 Oracle CONNECT BY 迁移到 postgresql 10

sql - 将 Oracle CONNECT BY 迁移到 postgresql 10

问题描述

我有这个 SQL：

SELECT count(*) as ct
          FROM classifications cls
WHERE
   cls.classification_id = :classification_id
START WITH cls.classification_id = :root_classification_id
CONNECT BY NOCYCLE PRIOR cls.classification_id = cls.parent_id

并需要将其迁移到 postgresql 10。

我已经安装了扩展 tablefunc 并尝试使用 connectedby。这是我的尝试：

SELECT count(*) as ct
          FROM classifications cls
WHERE
   cls.classification_id = :classification_id

union
                  SELECT count(classification_id) FROM connectby('classifications','classification_id','parent_id',:root_classification_id,5)
                  as t(classification_id varchar, parent_id varchar,level int)

问题是联合是错误的方式，因为那样你会得到 2 个计数结果。

标签： sqlpostgresqldatabase-migrationrecursive-query

解决方案

无需使用 tablefunc 扩展。这可以使用递归 CTE轻松完成

with recursive tree as (

  select cls.classification_id, cls.parent_id 
  from classifications cls
  where cls.classification_id = :root_classification_id

  union all

  select child.classification_id, child.parent_id
  from classifications child
     join tree parent on child.parent_id = parent.classification_id
)
select count(*)
from tree;

CTE 中的第一个查询与start withOracle 的start with. 第二个查询中返回到 CTE 的 JOIN 与connect byOracle 查询中的部分相匹配。

推荐阅读