MySQL CTE的用法|极客笔记

MySQL CTE的用法

什么是CTE？

CTE（Common Table Expression）通常被称为公共表表达式，是一种临时命名查询结果的方式。它允许我们在一个查询中创建一个临时的命名结果集，这个结果集可以被后续的查询引用和操作。

CTE的优点有：

使得复杂查询变得更加可读和易于维护
可以在多个查询中共享和引用结果集
更有效地组织和管理查询
可以减少查询中的重复代码

在MySQL 8.0之前，MySQL不支持CTE。但是从MySQL 8.0开始，CTE已经成为了MySQL的一部分。在MySQL中使用CTE可以通过WITH关键字来实现。

如何使用CTE？

创建CTE

我们可以通过下面的语法创建一个CTE：

WITH cte_name (column1, column2, ..., columnN) AS (
    SELECT column1, column2, ..., columnN
    FROM table_name
    WHERE condition
)

在这个语法中，cte_name是CTE的名称，column1, column2, ..., columnN是CTE的列名，可以选择性地指定。table_name是CTE的查询来源，condition是一个可选的查询条件。

引用CTE

一旦我们创建了CTE，我们可以在后续的查询中引用它。CTE可以在SELECT、INSERT、UPDATE和DELETE语句中使用。下面是一些示例：

在SELECT语句中使用CTE：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
)
SELECT column1, column2
FROM cte_name;

在INSERT语句中使用CTE：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
)
INSERT INTO another_table (column1, column2)
SELECT column1, column2
FROM cte_name;

在UPDATE语句中使用CTE：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
)
UPDATE table_name
SET column1 = cte_name.column1, column2 = cte_name.column2
FROM cte_name
WHERE table_name.id = cte_name.id;

在DELETE语句中使用CTE：

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
)
DELETE FROM table_name
WHERE table_name.id IN (SELECT id FROM cte_name);

CTE的递归使用

CTE还支持递归查询，这是CTE最强大的功能之一。递归查询通常用于处理层次结构的数据，例如组织结构和分类目录。

在CTE中进行递归查询需要定义一个初始查询（Anchor member）和一个递归查询（Recursive member）。

下面是一个简单的示例，使用递归CTE查询一个分类目录的层级关系：

WITH RECURSIVE cte_name (category_id, category_name, parent_id, level) AS (
    SELECT category_id, category_name, parent_id, 0
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT categories.category_id, categories.category_name, categories.parent_id, cte_name.level + 1
    FROM categories
    INNER JOIN cte_name ON categories.parent_id = cte_name.category_id
)
SELECT category_id, category_name, parent_id, level
FROM cte_name;

在这个示例中，我们首先选择所有parent_id为NULL的根节点作为初始查询（Anchor member），并设定层级为0。然后，在递归查询（Recursive member）中，我们通过INNER JOIN将查询结果与自身进行连接，直到满足递归终止条件。

CTE的使用注意事项

在使用CTE时，有一些注意事项需要牢记：

CTE只在其所属的查询中可见。它只在定义它的SELECT、INSERT、UPDATE或DELETE语句中可用。
CTE是只读的，不能对其进行UPDATE、DELETE或插入操作。
在定义CTE之后，可以在后续的查询中多次引用CTE。
CTE不支持在子查询中使用。
CTE的生命周期在相关查询执行完成后终止。

示例代码

下面是一个使用CTE的示例，我们创建一个包含两个表的示例数据库，其中一个表存储了员工的信息，另一个表存储了员工的部门信息。

-- 创建示例数据库
CREATE DATABASE company;
USE company;

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

-- 插入员工数据
INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 1, 5000),
(2, 'Bob', 1, 5500),
(3, 'Charlie', 2, 6000),
(4, 'David', 2, 7000),
(5, 'Eve', 3, 4500),
(6, 'Frank', 3, 4800);

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

-- 插入部门数据
INSERT INTO departments (id, name, manager_id) VALUES
(1, 'Sales', 1),
(2, 'Marketing', 3),
(3, 'Finance', 5);

示例1：查询每个部门的平均工资

我们可以使用CTE来查询每个部门的平均工资，并将结果与部门表进行连接，以获取部门名称。

WITH cte_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT cte_avg_salary.department_id, departments.name, cte_avg_salary.avg_salary
FROM cte_avg_salary
JOIN departments ON cte_avg_salary.department_id = departments.id;

运行结果：

| department_id | name       | avg_salary |
| ------------- | ---------- | ---------- |
| 1             | Sales      | 5250.00    |
| 2             | Marketing  | 6500.00    |
| 3             | Finance    | 4650.00    |

示例2：查询部门经理及其直接下属的信息

我们可以使用递归CTE来查询每个部门的经理和其直接下属的信息。

WITH RECURSIVE cte_hierarchy (id, name, department_id, level) AS (
    SELECT id, name, department_id, 0
    FROM employees
    WHERE id IN (
        SELECT manager_id
        FROM departments
    )
    UNION ALL
    SELECT employees.id, employees.name, employees.department_id, cte_hierarchy.level + 1
    FROM employees
    INNER JOIN cte_hierarchy ON employees.department_id = cte_hierarchy.id
)
SELECT cte_hierarchy.id, cte_hierarchy.name, departments.name AS department, cte_hierarchy.level
FROM cte_hierarchy
JOIN departments ON cte_hierarchy.department_id = departments.id;

运行结果：

| id | name    | department  | level |
| -- | ------- | ----------- | ----- |
| 1  | Alice   | Sales       | 0     |
| 2  | Bob     | Sales       | 1     |
| 3  | Charlie | Marketing   | 0     |
| 4  | David   | Marketing   | 1     |
| 5  | Eve     | Finance     | 0     |
| 6  | Frank   | Finance     | 1     |

在这个示例中，我们首先选择所有部门表中的经理ID，作为初始查询（Anchor member），并设置层级为0。然后，在递归查询（Recursive member）中，我们通过INNER JOIN将查询结果与自身进行连接，直到满足递归终止条件。