SQL - 公共表表达式 (CTE)

你好，未来的SQL大师们！今天，我们将踏上一段激动人心的旅程，探索公共表表达式，或者简称为CTE的世界。如果你是编程新手，不用担心——我会一步一步地引导你，就像我过去几年里教过的无数学生一样。所以，拿起你最喜欢的饮料，舒服地坐好，让我们一起开始吧！

SQL公共表表达式

想象一下你在组织一个大型派对（因为谁不喜欢一个好的类比呢？）。你有一份任务清单，想要把它们分解成更小、更易于管理的部分。这就是SQL中的公共表表达式所做的——它帮助我们分解复杂的查询，使其成为更简单、更易读的部分。

CTE就像是一个临时的命名结果集，你可以在SELECT、INSERT、UPDATE、DELETE或MERGE语句中引用它。它定义在单个语句的执行范围内。把它想象成为你查询创建的临时表。

让我们来看一个简单的例子：

WITH cte_example AS (
SELECT '你好，CTE！' AS greeting
)
SELECT greeting FROM cte_example;

在这个例子中：

1. 我们以WITH关键字开始，这标志着我们的CTE的开始。
2. 我们给我们的CTE起了一个名字：cte_example。
3. 我们定义了我们的CTE将包含的内容：在这个例子中，是一个简单的SELECT语句，创建了一个名为'greeting'的列，值为'你好，CTE！'。
4. 在CTE定义之后，是我们的主查询，它使用了CTE。

当你运行这个查询时，你会看到：

| greeting    |
|-------------|
| 你好，CTE！ |

这难道不酷吗？我们已经创建了我们的第一个CTE！

MySQL中的WITH子句

现在，让我们来谈谈MySQL中的WITH子句。它是让我们的CTE起作用的魔法棒。一般语法如下：

WITH cte_name [(column_list)] AS (query)
SELECT * FROM cte_name;

这里有一个更实际的例子。假设我们有一个员工表：

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'Finance', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'HR', 52000);

现在，让我们使用CTE来查找每个部门的平均工资：

WITH dept_avg_salary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM dept_avg_salary
ORDER BY avg_salary DESC;

这个查询会给我们：

| department | avg_salary |
|------------|------------|
| IT         | 62500.00   |
| Finance    | 55000.00   |
| HR         | 51000.00   |

在这里，我们使用CTE来计算每个部门的平均工资，然后从CTE中选择以显示结果。就像我们创建了一个包含平均工资的临时表，然后在我们主查询中使用它。

来自多个表的CTE

CTE不仅限于单个表。我们可以在CTE定义中使用多个表，就像在常规查询中一样。让我们在我们的例子中添加一个部门表：

CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, 'HR', '纽约'),
(2, 'IT', '旧金山'),
(3, 'Finance', '芝加哥');

现在，让我们使用CTE来组合两个表的信息：

WITH dept_info AS (
SELECT e.department,
AVG(e.salary) AS avg_salary,
d.location
FROM employees e
JOIN departments d ON e.department = d.name
GROUP BY e.department, d.location
)
SELECT * FROM dept_info
ORDER BY avg_salary DESC;

这将给我们：

| department | avg_salary | location      |
|------------|------------|---------------|
| IT         | 62500.00   | 旧金山       |
| Finance    | 55000.00   | 芝加哥       |
| HR         | 51000.00   | 纽约         |

在这个例子中，我们的CTE连接了员工和部门表，计算了平均工资，并包含了位置信息。

递归CTE

现在，让我们进入一个非常有趣的部分——递归CTE！这些就像SQL世界的俄罗斯套娃。递归CTE引用自身，允许你处理分层或树状结构的数据。

让我们创建一个简单的员工层次结构示例：

CREATE TABLE employee_hierarchy (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employee_hierarchy VALUES
(1, '大老板', NULL),
(2, '经理A', 1),
(3, '经理B', 1),
(4, '员工1', 2),
(5, '员工2', 2),
(6, '员工3', 3);

现在，让我们使用递归CTE来显示整个层次结构：

WITH RECURSIVE emp_hierarchy AS (
SELECT id, name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL

UNION ALL

SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employee_hierarchy e
JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy
ORDER BY level, id;

这个查询将产生：

| id | name       | manager_id | level |
|----|------------|------------|-------|
| 1  | 大老板   | NULL       | 0     |
| 2  | 经理A  | 1          | 1     |
| 3  | 经理B  | 1          | 1     |
| 4  | 员工1 | 2          | 2     |
| 5  | 员工2 | 2          | 2     |
| 6  | 员工3 | 3          | 2     |

这个递归CTE从顶层员工（大老板）开始，然后递归地找到每个经理的员工。

CTE的优点

CTE具有以下优点：

优点	描述
可读性	CTE通过将复杂查询分解为命名的子查询，使其更易于阅读。
可重用性	你可以在查询中多次引用一个CTE。
递归	CTE允许你编写递归查询，这对于层次数据非常有用。
简化	它可以简化复杂的连接和子查询。
维护性	CTE使查询更易于维护和修改。

CTE的缺点

尽管CTE功能强大，但它们确实有一些局限性：

缺点	描述
性能	在某些情况下，CTE的性能可能不如派生表或视图。
作用域	CTE仅在定义它们的单个语句范围内有效。
复杂性	对于非常简单的查询，使用CTE可能会增加不必要的复杂性。
数据库支持	并不是所有的数据库系统都支持CTE，尽管大多数现代数据库系统都支持。

好了，各位！我们已经穿越了公共表表达式的领域，从基本概念到递归查询。记住，像学习任何新技能一样，掌握CTE需要练习。所以不要害怕在你自己的查询中尝试它们。在你意识到之前，你将像专业人士一样编写CTE，让你的数据库查询更清晰、更高效。快乐查询！

Credits: Image by storyset