DB / Oracle / SQL 开发

Oracle 层次查询（Hierarchical Query）

2026.09.06

Oracle 层次查询（Hierarchical Query）

适用版本：Oracle Database 8i / 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

层次查询（Hierarchical Query） 用于查询树形结构数据[1]：

典型场景：

  • 组织架构
  • 物料清单（BOM）
  • 评论回复
  • 分类层级

2. 基本语法

SELECT ...
FROM table
START WITH condition
CONNECT BY [NOCYCLE] condition
[ORDER SIBLINGS BY column];

3. 基本示例

3.1 组织架构

-- 员工与经理关系
SELECT 
  employee_id,
  last_name,
  manager_id,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

3.2 字段说明

  • START WITH：根节点条件
  • CONNECT BY：父子关系
  • PRIOR：父行字段
  • LEVEL：层级（伪列）

4. LEVEL 伪列

4.1 缩进显示

SELECT 
  LPAD(' ', LEVEL * 2 - 2) || last_name AS name,
  LEVEL,
  employee_id,
  manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

4.2 过滤层级

-- 仅显示前 3 层
SELECT last_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
  AND LEVEL <= 3;

5. CONNECT_BY_ROOT

5.1 获取根节点

SELECT 
  last_name,
  CONNECT_BY_ROOT last_name AS root_manager,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

6. CONNECT_BY_ISLEAF

6.1 判断叶子节点

SELECT 
  last_name,
  CONNECT_BY_ISLEAF AS is_leaf,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- is_leaf: 1=叶子, 0=非叶子

7. SYS_CONNECT_BY_PATH

7.1 路径

-- 显示完整路径
SELECT 
  last_name,
  SYS_CONNECT_BY_PATH(last_name, ' -> ') AS path,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

7.2 输出

King -> King
King -> Jones
King -> Jones -> Scott
King -> Jones -> Scott -> Adams

8. NOCYCLE 与 CONNECT_BY_ISCYCLE

8.1 处理循环

-- 数据有循环时使用 NOCYCLE
SELECT 
  last_name,
  CONNECT_BY_ISCYCLE AS is_cycle,
  LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;
-- is_cycle: 1=有循环, 0=无

9. ORDER SIBLINGS BY

9.1 同级排序

SELECT 
  LPAD(' ', LEVEL * 2 - 2) || last_name AS name,
  salary
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY salary DESC;

10. 递归 CTE（11g R2+）

10.1 语法

WITH org_chart (employee_id, last_name, manager_id, lvl, path) AS (
  -- 起点
  SELECT 
    employee_id,
    last_name,
    manager_id,
    1 AS lvl,
    last_name AS path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归
  SELECT 
    e.employee_id,
    e.last_name,
    e.manager_id,
    oc.lvl + 1,
    oc.path || ' -> ' || e.last_name
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY path;

10.2 优势

  • ANSI 标准
  • 灵活
  • 支持复杂逻辑

11. 应用场景

11.1 BOM 展开

-- 物料清单
SELECT 
  LPAD(' ', LEVEL * 2 - 2) || part_name AS part,
  quantity,
  LEVEL
FROM bom
START WITH parent_id IS NULL
CONNECT BY PRIOR part_id = parent_id;

11.2 子树查询

-- 查询某节点的所有下属
SELECT employee_id, last_name, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

11.3 父树查询

-- 查询某员工的所有上级
SELECT employee_id, last_name, LEVEL
FROM employees
START WITH employee_id = 200
CONNECT BY PRIOR manager_id = employee_id;

12. 性能优化

12.1 索引

-- 父子关系列加索引
CREATE INDEX idx_emp_mgr ON employees(manager_id);
CREATE INDEX idx_emp_id ON employees(employee_id);

12.2 限制层级

-- 限制深度
SELECT * FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 5;

12.3 过滤起点

-- 缩小起点范围
SELECT * FROM employees
START WITH manager_id IS NULL AND dept_id = 10
CONNECT BY PRIOR employee_id = manager_id;

13. 常见坑与排错

13.1 ORA-01436: CONNECT BY 循环

修复：

-- 使用 NOCYCLE
SELECT * FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;

13.2 PRIOR 方向错误

-- 查下属
CONNECT BY PRIOR employee_id = manager_id
-- PRIOR 在父列

-- 查上级
CONNECT BY PRIOR manager_id = employee_id
-- PRIOR 在子列

13.3 WHERE 与 CONNECT BY 顺序

-- 执行顺序：
-- 1. WHERE（过滤所有行）
-- 2. START WITH
-- 3. CONNECT BY

-- 若要在树构建后过滤，用 CONNECT BY 中的条件

13.4 性能差

修复：

-- 1. 加索引
-- 2. 限制层级
-- 3. 缩小起点
-- 4. 使用递归 CTE

14. 最佳实践

  1. 索引父子列：提升性能
  2. 限制深度：避免无限递归
  3. 使用 NOCYCLE：防止循环错误
  4. ORDER SIBLINGS BY：保持层级顺序
  5. LEVEL 控制缩进：清晰显示
  6. SYS_CONNECT_BY_PATH：路径展示
  7. 复杂场景用 CTE：灵活
  8. 测试大数据量：验证性能
  9. PRIOR 方向正确：父子关系
  10. 过滤用 CONNECT BY：树构建后过滤

15. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Hierarchical Queries” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html

[2] Oracle Database Data Warehousing Guide 19c, “Recursive WITH Clause” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/