冠聪 發表於 2025-10-11 09:51:19

Oracle数据库递归查询示例详解

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">前言</a></li><li><a href="#_label1">一、核心概念与语法 (START WITH &hellip; CONNECT BY)</a></li><li><a href="#_label2">二、经典示例：员工组织架构图</a></li><li><a href="#_label3">三、其他有用的运算符和函数</a></li><li><a href="#_label4">四、ANSI SQL 标准方法：递归公用表表达式 (CTE)</a></li><li><a href="#_label5">五、两种方法的对比</a></li><li><a href="#_label6">总结</a></li></ul></div><p class="maodian"><a name="_label0"></a></p><h2>前言</h2>
<p>递归查询是 SQL 中非常强大的一种功能，它用于处理具有<strong>层次结构</strong>或<strong>树形结构</strong>的数据。在 Oracle 中，递归查询主要通过 <code>START WITH</code> 和 <code>CONNECT BY</code> 子句实现，这是 Oracle 的传统方法。从 Oracle 11g Release 2 开始，它也支持 ANSI SQL 标准的 <code>WITH</code> 子句（即公共表表达式 CTE）形式的递归查询。</p>
<p>我将重点介绍最常用、也是 Oracle 特色的 <code>START WITH ... CONNECT BY</code> 语法，并简要对比标准的递归 CTE 方法。</p>
<p class="maodian"><a name="_label1"></a></p><h2>一、核心概念与语法 (START WITH &hellip; CONNECT BY)</h2>
<p>这种语法专门为处理层次查询而设计，非常直观和高效。</p>
<p><strong>基本语法结构：</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT , column1, column2, ...
FROM table_name

START WITH condition -- 指定层次结构的根节点（起点）
CONNECT BY condition -- 定义父节点和子节点之间的关系
; -- 按兄弟节点排序
</pre></div>
<p><strong>关键元素解释：</strong></p>
<ol><li><p><strong>LEVEL 伪列</strong>：</p>
<ul><li>这是一个系统自动生成的伪列，它表示当前行在树形结构中的<strong>层级</strong>。</li><li>根节点的 <code>LEVEL</code> 为 1，根节点的直接子节点为 2，以此类推。</li></ul></li><li><p><strong>START WITH 子句</strong>：</p>
<ul><li>用于指定递归开始的<strong>根节点</strong>（一行或几行）。</li><li>例如：<code>START WITH employee_id = 100</code> 表示从员工 ID 为 100 的 CEO 开始构建树。</li></ul></li><li><p><strong>CONNECT BY 子句</strong>：</p>
<ul><li>这是递归查询的<strong>核心</strong>，它定义了父行和子行之间的<strong>关系</strong>。</li><li><strong>PRIOR 运算符</strong>：至关重要。它引用的是<strong>父行</strong>的列。<ul><li><code>CONNECT BY PRIOR child_id = parent_id</code>：表示<strong>上一行</strong>的 <code>child_id</code> 等于<strong>当前行</strong>的 <code>parent_id</code>。这通常用于从父节点向下遍历到子节点（自上而下）。</li><li><code>CONNECT BY child_id = PRIOR parent_id</code>：表示<strong>当前行</strong>的 <code>child_id</code> 等于<strong>上一行</strong>的 <code>parent_id</code>。这可以用于从子节点向上遍历到根节点（自下而上）。</li></ul></li></ul></li><li><p><strong>ORDER SIBLINGS BY 子句</strong>：</p>
<ul><li>在保持层次结构完整性的前提下，对同一父节点下的<strong>兄弟节点</strong>进行排序。</li><li>比直接在最后用 <code>ORDER BY</code> 更合理，因为它不会打乱树的显示顺序。</li></ul></li></ol>
<p class="maodian"><a name="_label2"></a></p><h2>二、经典示例：员工组织架构图</h2>
<p>假设我们有一个 <code>employees</code> 表，结构如下：</p>
<table><thead><tr><th>EMPLOYEE_ID</th><th>NAME</th><th>MANAGER_ID</th><th>JOB_TITLE</th></tr></thead><tbody><tr><td>100</td><td>King</td><td>(null)</td><td>President</td></tr><tr><td>101</td><td>Kochhar</td><td>100</td><td>VP</td></tr><tr><td>102</td><td>De Haan</td><td>100</td><td>VP</td></tr><tr><td>103</td><td>Hunold</td><td>102</td><td>Manager</td></tr><tr><td>104</td><td>Ernst</td><td>103</td><td>Analyst</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr></tbody></table>
<p><strong>需求：</strong> 查询所有员工，并显示他们的汇报层级关系。</p>
<p><strong>查询语句（自上而下）：</strong></p>
<div class="jb51code"><pre class="brush:sql;">SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1)*4, ' ') || NAME AS Indented_Name, -- 用缩进直观显示层级
    EMPLOYEE_ID,
    NAME,
    MANAGER_ID,
    JOB_TITLE
FROM employees
START WITH MANAGER_ID IS NULL -- 从最大的老板开始（没有经理的人）
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID -- 上一行的员工ID = 当前行的经理ID
ORDER SIBLINGS BY NAME; -- 同一经理下的员工按名字排序
</pre></div>
<p><strong>查询结果可能如下：</strong></p>
<table><thead><tr><th>LEVEL</th><th>Indented_Name</th><th>EMPLOYEE_ID</th><th>NAME</th><th>MANAGER_ID</th><th>JOB_TITLE</th></tr></thead><tbody><tr><td>1</td><td>King</td><td>100</td><td>King</td><td>(null)</td><td>President</td></tr><tr><td>2</td><td>De Haan</td><td>102</td><td>De Haan</td><td>100</td><td>VP</td></tr><tr><td>3</td><td>Hunold</td><td>103</td><td>Hunold</td><td>102</td><td>Manager</td></tr><tr><td>4</td><td>Ernst</td><td>104</td><td>Ernst</td><td>103</td><td>Analyst</td></tr><tr><td>2</td><td>Kochhar</td><td>101</td><td>Kochhar</td><td>100</td><td>VP</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr></tbody></table>
<p>从这个结果可以清晰地看出 King 是根节点，De Haan 和 Kochhar 向他汇报，Hunold 向 De Haan 汇报，Ernst 向 Hunold 汇报。</p>
<p class="maodian"><a name="_label3"></a></p><h2>三、其他有用的运算符和函数</h2>
<ol><li><p><code>CONNECT_BY_ROOT</code>：</p>
<ul><li>用于获取当前行所在树的<strong>根节点</strong>的某列值。</li><li><code>SELECT CONNECT_BY_ROOT NAME AS Top_Manager, NAME ...</code> 会为 Ernst 显示 <code>Top_Manager</code> 是 <code>King</code>。</li></ul></li><li><p><code>SYS_CONNECT_BY_PATH</code>：</p>
<ul><li>显示从根节点到当前节点的<strong>完整路径</strong>。</li><li><code>SELECT SYS_CONNECT_BY_PATH(NAME, &#39; -&gt; &#39;) AS Path ...</code> 对于 Ernst，会显示 <code> -&gt; King -&gt; De Haan -&gt; Hunold -&gt; Ernst</code>。</li></ul></li><li><p><code>CONNECT_BY_ISLEAF</code>：</p>
<ul><li>判断当前行是否是<strong>叶子节点</strong>（即没有子节点）。是叶子节点则返回 1，否则返回 0。</li></ul></li></ol>
<p class="maodian"><a name="_label4"></a></p><h2>四、ANSI SQL 标准方法：递归公用表表达式 (CTE)</h2>
<p>Oracle 也支持使用 <code>WITH</code> 子句进行递归查询，语法更符合其他数据库（如 PostgreSQL, SQL Server）的标准。</p>
<p><strong>语法结构：</strong></p>
<div class="jb51code"><pre class="brush:sql;">WITH cte_name (column_list) AS (
    -- 锚定成员 (Anchor Member)：定义根节点
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition -- 类似于 START WITH

    UNION ALL

    -- 递归成员 (Recursive Member)：引用CTE自身，进行递归join
    SELECT t.column1, t.column2, ...
    FROM table_name t
    JOIN cte_name c ON t.parent_id = c.child_id -- 类似于 CONNECT BY
)
-- 主查询
SELECT * FROM cte_name;
</pre></div>
<p><strong>用递归 CTE 实现上面的例子：</strong></p>
<div class="jb51code"><pre class="brush:sql;">WITH Employee_Tree (LEVEL, EMPLOYEE_ID, NAME, MANAGER_ID, JOB_TITLE) AS (
    -- 锚定成员：找到根节点
    SELECT
      1 AS LEVEL,
      EMPLOYEE_ID,
      NAME,
      MANAGER_ID,
      JOB_TITLE
    FROM employees
    WHERE MANAGER_ID IS NULL

    UNION ALL

    -- 递归成员：连接员工表和CTE自身
    SELECT
      p.LEVEL + 1, -- 层级增加
      e.EMPLOYEE_ID,
      e.NAME,
      e.MANAGER_ID,
      e.JOB_TITLE
    FROM employees e
    INNER JOIN Employee_Tree p ON e.MANAGER_ID = p.EMPLOYEE_ID
)
SELECT * FROM Employee_Tree
ORDER BY LEVEL, NAME;
</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>五、两种方法的对比</h2>
<table><thead><tr><th>特性</th><th>START WITH ... CONNECT BY (Oracle专用)</th><th>递归 CTE WITH (ANSI 标准)</th></tr></thead><tbody><tr><td><strong>语法简洁性</strong></td><td><strong>更简洁</strong>，专为层次查询设计</td><td>稍显冗长，但逻辑清晰</td></tr><tr><td><strong>功能强大性</strong></td><td><strong>非常强大</strong>，有专属伪列和函数(<code>LEVEL</code>, <code>SYS_CONNECT_BY_PATH</code>等)</td><td>功能同样强大，但需要自己实现类似功能（如用字段记录Path）</td></tr><tr><td><strong>可读性</strong></td><td>对熟悉 Oracle 的人可读性高</td><td>遵循声明式编程，递归逻辑更标准，对来自其他数据库的用户可读性高</td></tr><tr><td><strong>性能</strong></td><td><strong>通常性能更优</strong>，Oracle 对其有深度优化</td><td>性能也很好，但可能不如原生语法</td></tr><tr><td><strong>标准性</strong></td><td>Oracle 私有语法</td><td><strong>ANSI SQL 标准</strong>，可移植性好</td></tr></tbody></table>
<p class="maodian"><a name="_label6"></a></p><h2>总结</h2>
<ul><li>对于 Oracle 环境下的开发，<strong>START WITH ... CONNECT BY</strong> 是处理递归查询的<strong>首选</strong>，因为它语法简洁、功能专一且性能优异。</li><li>如果你需要编写<strong>跨数据库</strong>兼容的 SQL，或者希望递归逻辑更符合通用的编程思维（先锚定再递归），那么应该使用<strong>递归 CTE</strong> (<code>WITH</code> 子句)。</li></ul>
<p>无论是哪种方法，递归查询都是操作树形结构数据（如组织架构、菜单、分类目录、BOM物料清单）的利器。</p>
頁: [1]
查看完整版本: Oracle数据库递归查询示例详解