My Note

Appearance

Oracle 构建树形结构的两种方式 ​

在 Oracle 数据库中，构建树形结构有两种常见方法：CONNECT BY 和 WITH RECURSIVE。下面将详细介绍这两种方法的语法、优缺点及适用场景，帮助你选择最合适的方式。

示例 ​

假设有一张 categories 表，结构如下：

  • id：节点 ID
  • parent_id：父节点 ID
  • name：节点名称

查询树形结构：

idparent_idnamedepth
1NULLRoot Node1
21Child Node12
31Child Node22
42SubChild13

方法 1: CONNECT BY 方式 ​

CONNECT BY 是 Oracle 的传统递归查询方式，广泛应用于构建树形结构。

语法 ​
sql
SELECT 
    id,
    parent_id,
    name,
    LEVEL AS depth
FROM 
    categories
START WITH 
	-- 根节点条件
    parent_id IS NULL
CONNECT BY 
	-- 递归条件：当前节点的 parent_id 对应父节点的 id
    PRIOR id = parent_id;
关键点 ​
  • START WITH：指定递归查询的起点（通常是根节点）。
  • CONNECT BY：定义递归条件，PRIOR 用于表示父子关系。PRIOR 表示前一行的值。
  • LEVEL：内置伪列，表示树的深度（从 1 开始）。
优点 ​
  • 简洁，能够高效地查询树形结构。
  • 在 Oracle 环境下，性能较好，适合大多数简单递归查询。
缺点 ​
  • 语法依赖于 Oracle，无法轻易迁移到其他数据库。
  • 对于复杂递归逻辑，不如 WITH RECURSIVE 直观。

方法 2: WITH RECURSIVE 方式 ​

从 Oracle 11g 版本开始，支持标准 SQL 的递归查询方式 WITH RECURSIVE，这种方法与 MySQL、PostgreSQL 等数据库兼容。

语法 ​
sql
WITH category_tree (id, parent_id, name, depth) AS (
    -- 锚点查询：根节点
    SELECT id, parent_id, name, 0 AS depth
    FROM categories
    -- 根节点条件
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询：查找子节点
    SELECT c.id, c.parent_id, c.name, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
关键点 ​
  • WITH RECURSIVE：递归公用表表达式（CTE），在递归部分引用自己，形成递归查询。
  • UNION ALL：递归部分将查询结果合并，直到满足递归条件。
  • depth：自定义字段，表示树的层级。
优点 ​
  • 符合 SQL 标准，语法更清晰，递归结构更直观。
  • 可以方便地迁移到其他数据库，如 MySQL、PostgreSQL 等。
  • 更适合处理复杂的递归查询，扩展性更好。
缺点 ​
  • 性能上可能稍逊于 CONNECT BY，但对于大多数应用场景差异不大。
  • 仅在 Oracle 11g 及以上版本支持，较旧的版本不支持。

对比总结 ​

特性CONNECT BYWITH RECURSIVE
语法Oracle 特定，传统方式标准 SQL 语法，支持跨数据库
可读性略复杂，递归关系通过 PRIOR 表达语法清晰，递归结构明显，易理解
移植性仅适用于 Oracle支持多种数据库，如 MySQL、PostgreSQL、SQL Server
性能较高效，适合大多数树形查询性能稍逊，但差异不大
深度控制使用内置的 LEVEL 伪列可自定义深度字段
应用场景简单树形查询，尤其是老项目现代化查询，跨数据库兼容性，复杂递归逻辑

结论 ​

  • CONNECT BY：适用于在 Oracle 环境下需要快速构建树形结构的场景。它更简洁，查询性能更高，适合大多数树形查询。
  • WITH RECURSIVE：适用于需要跨数据库迁移的场景，或者当查询逻辑复杂时，它的递归结构更清晰，且符合 SQL 标准。