跳到主要内容

MySQL 公用表表达式

MySQL公用表表达式（Common Table Expressions，简称CTE）是一种临时命名的结果集，可以在单个查询中多次引用。CTE使得复杂的SQL查询更加清晰和易于维护，特别适用于需要递归查询的场景。

什么是公用表表达式？​

公用表表达式（CTE）是一个临时的结果集，它在查询执行期间存在。CTE通常用于简化复杂的查询，尤其是那些需要多次引用相同子查询的情况。CTE的语法如下：

sql
WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

基本语法​

  • WITH 关键字用于定义CTE。
  • cte_name 是CTE的名称，可以在后续查询中引用。
  • AS 关键字后面是CTE的定义，通常是一个SELECT语句。

使用CTE的好处​

  1. 提高可读性：CTE可以将复杂的查询分解为多个简单的部分，使查询更易于理解。
  2. 避免重复代码：CTE可以在查询中多次引用，避免了重复编写相同的子查询。
  3. 支持递归查询：CTE特别适合处理递归查询，例如树形结构的数据。

基本示例​

假设我们有一个名为 employees 的表，结构如下：

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

我们想要查询每个员工及其经理的名字。可以使用CTE来实现：

sql
WITH ManagerCTE AS (
    SELECT e.id, e.name, m.name AS manager_name
    FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.id
)
SELECT * FROM ManagerCTE;

输出示例​

idnamemanager_name
1AliceNULL
2BobAlice
3CharlieBob

递归CTE​

递归CTE是CTE的一个强大特性，特别适用于处理层次结构数据，例如组织结构、目录树等。

递归CTE语法​

sql
WITH RECURSIVE cte_name AS (
    -- 初始查询
    SELECT ...
    UNION ALL
    -- 递归查询
    SELECT ... FROM cte_name WHERE ...
)
SELECT ... FROM cte_name;

递归CTE示例​

假设我们想要查询某个员工的所有下属（包括间接下属），可以使用递归CTE：

sql
WITH RECURSIVE SubordinatesCTE AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1  -- 假设我们要查询ID为1的员工的所有下属
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN SubordinatesCTE s ON e.manager_id = s.id
)
SELECT * FROM SubordinatesCTE;

输出示例​

idnamemanager_id
1AliceNULL
2Bob1
3Charlie2

实际应用场景​

场景1：组织结构查询​

在一个公司中，每个员工都有一个经理，形成一个树形结构。使用递归CTE可以轻松查询某个员工的所有下属。

场景2：目录树查询​

在一个商品分类系统中，每个分类可能有子分类。使用递归CTE可以查询某个分类下的所有子分类。

总结​

MySQL的公用表表达式（CTE）是一种强大的工具，可以显著提高复杂查询的可读性和可维护性。通过使用CTE，你可以避免重复代码，简化递归查询，并更清晰地表达查询逻辑。

提示

在使用CTE时，尽量将其用于需要多次引用的子查询或递归查询场景，以充分发挥其优势。

附加资源​

练习​

  1. 尝试在 employees 表中添加更多数据，并使用CTE查询某个员工的所有上级。
  2. 使用递归CTE查询一个商品分类系统中的所有子分类。

通过练习，你将更深入地理解CTE的使用方法和实际应用场景。