Oracle高级技术——CTE

Good Day~

        今天聊个Oracle的CTE，刚听到这个会不会有点陌生？CTE是英文Common Table Expressions（公共表表达式）。这个概念其实用的还是蛮多的，解决的主要是将尽可能多的子查询减少，简化复杂查询，比如我们平时用left Join或者Right Join 可能一连连一堆表，这样重复的子查询也可能很多，在运行的时候很费性能。

        废话不多说，直接上代码。

   首先CTE的原理是生成SQL语句运行范围内的临时视图，以让这个临时视图可以被反复运用。我们来看看它的基础结构，所有CTE的结构都是基于此基础结构~

//代码01WITH dept_salary AS (    SELECT         d.department_id,        d.department_name,        COUNT(e.employee_id) AS employee_count,        SUM(e.salary) AS total_salary,        ROUND(AVG(e.salary), 2) AS avg_salary    FROM cte_test_departments d    LEFT JOIN cte_test_employees e ON d.department_id = e.department_id    GROUP BY d.department_id, d.department_name)SELECT     department_name,    employee_count,    total_salary,    avg_salaryFROM dept_salaryORDER BY total_salary DESC;

        可以看出基本语法结构非常短~

【with 临时视图名称 as (查询语句)做正式查询(可用临时视图） 】

        让我们通过以上语句来了解CTE结构~首先我们刚刚说了CTE是通过创建临时视图做复用以提升复杂查询的性能，所以以上代码01 的with后面跟着的就是临时视图的名称，然后临时视图的代表结果集则是As后面的括号内的select查询。这个临时视图产生的结果集在以下的SQL语句一起运行时才有效，如果你要单独跑这个视图，它会像报正常的错误一样说表不存在，所以如果你要跑真正的查询，你得包含着临时视图的创建一起跑，而且无论你做多少个查询，只要你包含着临时视图一起跑都是可以跑的，因为临时视图依赖于SQL语句才产生作用，不然啥用没有。这就是这个语句的全部原理。

        根据这个原理，我们可以得出临时视图可以反复复用，这样对复杂查询非常有效。我们还知道临时视图的有效性只存在于单句SQL查询中。

        说了这个，有没有觉得有点熟悉？是的，其实有点像存储过程，但是比存储过程了解起来更加简单。

  聊另一个核心功能~递归层级显示，也就是如果你有多个视图，你可以通过CTE非常简单的实现业务的递归查询。上个代码来看看~

图片

        递归层级显示是CTE非常重要的一点，让我们来详细聊聊每个步骤~

这个递归CTE查询的执行分为以下几个关键阶段：

1. 初始阶段（锚成员执行）

-- 基础查询（顶级部门）先执行SELECT department_id,department_name,parent_id,department_name AS path,1 AS level_depthFROM cte_test_departmentsWHERE parent_id ISNULL

这部分称为"锚成员"(Anchor Member)，它会首先执行并产生初始结果集。根据测试数据，这部分会返回：

图片

2. 第一次递归（UNION ALL后的部分）

有了初始结果后，递归部分开始执行：

-- 使用上一步的结果(只有"总部"记录)进行连接SELECT  d.department_id, d.department_name,  d.parent_id,  h.path ||' > '|| d.department_name,   h.level_depth +1FROM cte_test_departments d JOIN dept_hierarchy h ON d.parent_id = h.department_id

此时dept_hierarchy只有一条记录（总部，department_id=1），所以会查找所有parent_id=1的部门：（由于分开解析，所以直接代入子查询）

图片

3. 第二次递归

现在dept_hierarchy包含3条记录：总部 (level 1),技术部 (level 2),市场部 (level 2)

递归部分再次执行，这次会找到：

parent_id=2（技术部）的部门：研发中心(4)、技术支持(5)

parent_id=3（市场部）的部门：数字营销(6)、传统营销(7)

图片

4. 第三次递归

现在检查这些level=3的部门是否有子部门（数据显示没有部门以4,5,6,7作为parent_id），所以递归终止。

        最终UNION ALL将所有结果合并：

图片

  从结果中来看，递归CTE是迭代执行的，先执行锚成员获得种子数据，然后反复执行递归部分，每次使用前一次的结果直到递归部分返回空集时停止~

        而UNION ALL后的dept_hierarchy：第一次执行时，它引用的就是锚成员的结果，后续每次执行，它引用的是前几次结果的累积。

而递归终止条件，当递归部分不再产生新行时自动终止，也可以使用WHERE level_depth < N人工限制深度。这种机制使得递归CTE非常适合处理树形或层次结构数据，比传统的CONNECT BY语法更符合SQL标准且更灵活。

        讲一百遍不如实践一遍，你看懂了吗？

谢谢你看到这，祝你生活愉快~

欢迎关注我的公众号【会用数据库】