MySQL：CTE 通用表达式
在MySQL中，CTE（Common Table Expressions，通用表表达式）是一种简洁而强大的语法，用于构建临时的结果集，通常用于简化复杂查询。CTE通过关键字 WITH引入，定义一个可以在后续查询中引用的临时结果集。CTE有两种类型：普通CTE和递归CTE。
一、普通CTE
普通CTE用于定义一个非递归的临时结果集，通常用于提高查询的可读性和维护性。
1. 语法
普通CTE的基本语法如下：
WITH cte_name (column1, column2, ...)
AS
(
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;
2. 示例
假设我们有一个名为 employees的表，结构如下：
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(100),
dept_id INT,
salary DECIMAL(10, 2)
);
INSERT INTO employees (emp_id, emp_name, dept_id, salary) VALUES
(1, 'Alice', 1, 5000.00),
(2, 'Bob', 2, 6000.00),
(3, 'Charlie', 1, 5500.00),
(4, 'David', 3, 7000.00),
(5, 'Eve', 2, 6500.00);
我们希望查询部门ID为1的所有员工信息，可以使用普通CTE：
WITH dept1_employees AS (
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = 1
)
SELECT *
FROM dept1_employees;
该查询结果为：
+--------+----------+--------+
| emp_id | emp_name | salary |
+--------+----------+--------+
| 1 | Alice | 5000.00|
| 3 | Charlie | 5500.00|
+--------+----------+--------+
二、递归CTE
递归CTE用于解决具有层级关系的数据查询问题，如组织结构、类别层级等。递归CTE由两个部分组成：锚定成员（非递归部分）和递归成员。
1. 语法
递归CTE的基本语法如下：
WITH RECURSIVE cte_name (column1, column2, ...)
AS
(
-- 锚定成员
SELECT column1, column2, ...
FROM table_name
WHERE condition
UNION ALL
-- 递归成员
SELECT column1, column2, ...
FROM cte_name
JOIN table_name ON condition
)
SELECT *
FROM cte_name;
2. 示例
假设我们有一个名为 employees的表，包含员工及其经理的信息：
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(100),
manager_id INT
);
INSERT INTO employees (emp_id, emp_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 1),
(5, 'Eve', 3);
我们希望查询员工Alice的所有直接和间接下属，可以使用递归CTE：
WITH RECURSIVE subordinates AS (
-- 锚定成员：Alice本身
SELECT emp_id, emp_name, manager_id
FROM employees
WHERE emp_name = 'Alice'
UNION ALL
-- 递归成员：Alice的下属及其下属
SELECT e.emp_id, e.emp_name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT *
FROM subordinates;
该查询结果为：
+--------+----------+------------+
| emp_id | emp_name | manager_id |
+--------+----------+------------+
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 2 |
| 4 | David | 1 |
| 5 | Eve | 3 |
+--------+----------+------------+
三、CTE的优势
- 简洁性：CTE可以将复杂查询分解为多个易于理解的部分，提高代码的可读性。
- 复用性：定义一次CTE，可以在后续查询中多次引用，避免重复代码。
- 递归查询：递归CTE提供了一种优雅的方式来处理层级结构的数据。
四、思维导图
CTE 通用表达式
│
├── 普通CTE
│ ├── 语法
│ └── 示例
│ └── 查询部门ID为1的员工信息
│
├── 递归CTE
│ ├── 语法
│ └── 示例
│ └── 查询员工Alice的所有下属
│
└── 优势
├── 简洁性
├── 复用性
└── 递归查询
五、总结
CTE（通用表表达式）为处理复杂查询提供了强大的工具。通过普通CTE，可以简化查询逻辑，提高可读性；通过递归CTE，可以优雅地处理层级结构数据。掌握CTE的使用，对于提升SQL查询能力和优化数据库操作有着重要意义。希望本文能帮助你更好地理解和使用MySQL中的CTE，提高工作效率和代码质量。
