MySQL高级讲座篇之：CTE（Common Table Expressions）：在复杂查询中实现代码的模块化与可读性。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位，早上好！今天咱们不搞那些虚头巴脑的，直接上干货，聊聊MySQL里一个挺好使的家伙——CTE，也就是Common Table Expressions，中文名叫公共表表达式。听起来高大上，其实就是个给复杂查询瘦身、让代码更易读的小帮手。

为啥要用CTE？

你想啊，SQL这玩意儿，写简单了还行，一旦涉及到多个表连接、嵌套子查询，那代码就像一团乱麻，自己过几天都看不懂，更别说让别人接手了。举个例子，假设咱们要做一个查询，要找到每个部门收入最高的员工，以及他/她的收入占该部门总收入的百分比。没有CTE，你可能要写成这样（代码仅用于演示，可能不是最优写法）：

SELECT
    e.employee_name,
    e.salary,
    d.department_name,
    (e.salary / dept_total.total_salary) * 100 AS percentage_of_dept_salary
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
JOIN
    (SELECT
        department_id,
        SUM(salary) AS total_salary
    FROM
        employees
    GROUP BY
        department_id) AS dept_total ON e.department_id = dept_total.department_id
WHERE e.salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id)
ORDER BY d.department_name;

这段代码，光是看就头大。里面嵌套了一个计算部门总收入的子查询，然后在WHERE子句里又用了一个子查询找到每个部门的最高工资。读起来费劲，维护起来更要命。如果用CTE，代码可以变得更清晰：

WITH
    DeptTotalSalaries AS (
        SELECT
            department_id,
            SUM(salary) AS total_salary
        FROM
            employees
        GROUP BY
            department_id
    ),
    MaxSalaryPerDept AS (
        SELECT
            department_id,
            MAX(salary) AS max_salary
        FROM
            employees
        GROUP BY
            department_id
    )
SELECT
    e.employee_name,
    e.salary,
    d.department_name,
    (e.salary / dts.total_salary) * 100 AS percentage_of_dept_salary
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
JOIN
    DeptTotalSalaries dts ON e.department_id = dts.department_id
JOIN
    MaxSalaryPerDept mspd ON e.department_id = mspd.department_id AND e.salary = mspd.max_salary
ORDER BY d.department_name;

咋样？是不是感觉清爽多了？咱们定义了两个CTE：DeptTotalSalaries用来计算部门总收入，MaxSalaryPerDept用来找到每个部门的最高工资。然后，在主查询里直接引用这两个CTE，就像引用普通的表一样。这样一来，代码的逻辑结构更清晰，每个部分的功能也更明确了。

CTE的语法

CTE的语法很简单，就两步：

用WITH关键字开头，后面跟一个或多个CTE的定义，每个CTE之间用逗号,分隔。
每个CTE的定义包括一个CTE名称和一个AS关键字，后面跟着一个用括号括起来的SELECT语句。

WITH
    CTE1 AS (
        SELECT ...
    ),
    CTE2 AS (
        SELECT ...
    )
SELECT ... FROM CTE1 JOIN CTE2 ...;

CTE的类型

CTE主要有两种类型：

非递归CTE（Non-recursive CTE）: 这是最常见的CTE，就像咱们上面举的例子那样。每个CTE只执行一次，而且不能引用自身。
递归CTE（Recursive CTE）: 这种CTE比较特殊，它可以引用自身。递归CTE通常用于处理层级结构的数据，比如树形结构、组织结构等等。

非递归CTE的常见用法

除了上面那个找到每个部门收入最高的员工的例子，非递归CTE还有很多其他用途：

简化复杂的JOIN操作：可以将多个JOIN操作分解成多个CTE，让代码更易读。
在单个查询中多次使用相同的子查询结果：避免重复计算，提高查询效率。
生成临时表：虽然CTE不是真正的临时表，但它可以起到类似的作用，方便在查询中使用。

递归CTE

递归CTE是CTE里的一朵奇葩，它可以自己调用自己。递归CTE由两部分组成：

锚成员（Anchor Member）: 这是递归的起点，它是一个非递归的SELECT语句，用来初始化结果集。
递归成员（Recursive Member）: 这是一个SELECT语句，它引用CTE自身，并根据锚成员的结果集进行迭代计算。

递归CTE的语法如下：

WITH RECURSIVE
    cte_name AS (
        -- 锚成员（Anchor Member）
        SELECT ...
        UNION ALL
        -- 递归成员（Recursive Member）
        SELECT ... FROM cte_name WHERE ...
    )
SELECT ... FROM cte_name;

注意：

必须使用WITH RECURSIVE关键字来声明这是一个递归CTE。
锚成员和递归成员之间必须用UNION ALL连接。UNION会去重，而递归的过程中通常需要保留重复数据。
递归成员必须引用CTE自身。
递归成员必须有一个终止条件，否则递归会无限循环下去。

递归CTE的例子：生成数字序列

咱们先来一个简单的例子，用递归CTE生成一个从1到10的数字序列：

WITH RECURSIVE NumberSeries AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM NumberSeries WHERE n < 10
)
SELECT n FROM NumberSeries;

这个CTE的执行过程是这样的：

锚成员SELECT 1 AS n初始化结果集，结果集中包含一个数字1。
递归成员SELECT n + 1 FROM NumberSeries WHERE n < 10从NumberSeries中读取数据，将每个数字加1，然后添加到结果集中。
递归成员会一直执行，直到n不再小于10为止。

最终，NumberSeries包含从1到10的所有数字。

递归CTE的例子：查找员工的所有上级

再来一个稍微复杂点的例子，假设咱们有一个员工表，其中包含员工ID、员工姓名和上级ID。现在，咱们要用递归CTE找到某个员工的所有上级。

首先，创建一个示例表：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, '张三', NULL),
(2, '李四', 1),
(3, '王五', 2),
(4, '赵六', 2),
(5, '钱七', 1),
(6, '孙八', 5);

然后，使用递归CTE查找员工’王五’的所有上级：

WITH RECURSIVE EmployeeHierarchy AS (
    -- 锚成员：找到指定的员工
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employees
    WHERE employee_name = '王五'
    UNION ALL
    -- 递归成员：找到该员工的上级
    SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.employee_id = eh.manager_id
)
SELECT employee_id, employee_name, manager_id, level
FROM EmployeeHierarchy;

这个CTE的执行过程是这样的：

锚成员SELECT employee_id, employee_name, manager_id, 0 AS level FROM employees WHERE employee_name = '王五'初始化结果集，结果集中包含’王五’的信息，level为0。
递归成员SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1 FROM employees e JOIN EmployeeHierarchy eh ON e.employee_id = eh.manager_id从EmployeeHierarchy中读取数据，找到’王五’的上级，然后添加到结果集中，level加1。
递归成员会一直执行，直到找不到上级为止。

最终，EmployeeHierarchy包含’王五’及其所有上级的信息，以及他们之间的层级关系。

CTE的性能考虑

虽然CTE可以提高代码的可读性，但有时候也会影响查询性能。在MySQL 8.0版本之前，CTE会被物化（Materialization），也就是说，MySQL会把CTE的结果集存储到临时表中，然后再在主查询中使用。这种物化操作会增加I/O开销，降低查询效率。

但是，从MySQL 8.0版本开始，MySQL对CTE进行了优化，允许将CTE的结果集直接传递给主查询，而不需要物化。这种优化可以显著提高CTE的性能。

当然，即使在MySQL 8.0版本中，CTE也可能被物化。如果CTE包含复杂的表达式、聚合函数或者DISTINCT关键字，MySQL仍然可能会选择物化CTE。

为了提高CTE的性能，可以考虑以下几点：

尽量使用简单的CTE，避免在CTE中包含复杂的表达式和聚合函数。
如果CTE的结果集比较大，可以考虑使用索引来加速查询。
如果可能，尽量避免在CTE中使用DISTINCT关键字。
确保你的MySQL版本是8.0或更高版本，以便享受CTE的优化。

CTE vs. 临时表

CTE和临时表都可以用来存储中间结果，但它们之间有一些区别：

特性	CTE	临时表
存储	虚拟的，可能不物化	实际的，存储在磁盘或内存中
作用域	仅在当前查询中可见	在当前会话中可见
生命周期	查询结束后自动销毁	手动删除或会话结束时销毁
性能	可能更快，取决于优化器	可能更慢，取决于I/O开销
语法	使用`WITH`关键字定义	使用`CREATE TEMPORARY TABLE`定义

总的来说，CTE更适合于简单的、只在当前查询中使用的中间结果。临时表更适合于复杂的、需要在多个查询中使用的中间结果。

总结

CTE是一个非常实用的工具，可以帮助咱们编写更清晰、更易读的SQL代码。无论是简化复杂的JOIN操作，还是处理层级结构的数据，CTE都能派上用场。当然，在使用CTE的时候，也要注意性能问题，尽量避免在CTE中包含复杂的表达式和聚合函数。希望今天的讲座能让你对CTE有更深入的了解，并在实际工作中灵活运用。

好了，今天的分享就到这里，有问题可以随时提问。下课！

发表回复 取消回复

发表回复取消回复