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          |
+--------+----------+------------+