Oracle CONNECT BY 转换为 PostgreSQL 递归 CTE

在日常数据库开发中，经常会遇到树形结构的部门、菜单、分类等数据。 在 Oracle 中我们可以很方便地使用 START WITH … CONNECT BY PRIOR 来实现层次查询，但在 PostgreSQL 中并不支持该语法，需要改写成 递归 CTE (WITH RECURSIVE)。

本文就以一个 部门表 为例，演示如何从 Oracle 写法迁移到 PostgreSQL。

示例场景

我们有一张 部门表 dept_info，主要字段如下：

另外还有一张表 project_line，里面保存了某些项目所属的部门，需要以此为入口查出完整的部门树。

Oracle 写法

在 Oracle 中，层次查询可以直接写成：

SELECT d.dept_code,
       d.dept_name,
       d.dept_short,
       d.dept_level,
       d.sort_no,
       d.dept_id,
       d.parent_id
FROM dept_info d
START WITH d.dept_id IN (SELECT line_dept FROM project_line)
CONNECT BY PRIOR d.dept_id = d.parent_id
ORDER BY d.sort_no;

这里的逻辑是：

• START WITH：找到递归的起点（项目所属部门）。
• CONNECT BY PRIOR：递归关系定义，这里是“上级部门 → 下级部门”。
• ORDER BY：结果集排序。

PostgreSQL 改写

在 PostgreSQL 中没有 CONNECT BY，需要用 递归 CTE 来实现：

WITH RECURSIVE dept_hierarchy AS (
    -- 基准查询（对应 START WITH）
    SELECT 
        d.dept_id,
        d.dept_code,
        d.dept_name,
        d.dept_short,
        d.dept_level,
        d.sort_no,
        d.parent_id,
        1 AS level  -- 起始层级
    FROM dept_info d
    WHERE d.dept_id IN (SELECT line_dept FROM project_line)

    UNION ALL

    -- 递归查询（对应 CONNECT BY PRIOR）
    SELECT 
        c.dept_id,
        c.dept_code,
        c.dept_name,
        c.dept_short,
        c.dept_level,
        c.sort_no,
        c.parent_id,
        p.level + 1 AS level
    FROM dept_info c
    INNER JOIN dept_hierarchy p ON p.dept_id = c.parent_id
)
SELECT *
FROM dept_hierarchy
ORDER BY sort_no;

关键点说明

1. WITH RECURSIVE 开启递归 CTE 的定义。

2. 初始 SELECT 对应 START WITH，定义递归的起点。

3. 递归部分 使用 UNION ALL 拼接子节点，对应 Oracle 的 CONNECT BY。

4. 层级 level PostgreSQL 没有内置的 LEVEL 伪列，可以手动在递归时 p.level + 1 来实现。

5. 循环防护（可选） 如果数据存在环路，可以在递归部分增加约束，例如：

   WHERE c.dept_id <> ALL(ARRAY[p.dept_id])
   

   来避免死循环。

总结

• 在 Oracle 中，树形查询依赖 CONNECT BY，语法简洁。
• 在 PostgreSQL 中，需要改写为 递归 CTE，逻辑更清晰，也更符合 SQL 标准。
• 迁移时要特别注意 层级 (level) 和 循环防护 的写法。

✅ 到这里，你就可以轻松地把 Oracle 的树形查询 SQL 迁移到 PostgreSQL 了。