SQL - Common Table Expression (CTE)

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

SQL公共表表达式

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

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

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

WITH cte_example AS (
SELECT 'Hello, CTE!' AS greeting
)
SELECT greeting FROM cte_example;

在这个例子中：

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

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

| greeting    |
|-------------|
| Hello, 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', 'New York'),
(2, 'IT', 'San Francisco'),
(3, 'Finance', 'Chicago');

现在，让我们使用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   | San Francisco |
| Finance    | 55000.00   | Chicago       |
| HR         | 51000.00   | New York      |

在这个例子中，我们的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, 'Big Boss', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee 1', 2),
(5, 'Employee 2', 2),
(6, 'Employee 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  | Big Boss   | NULL       | 0     |
| 2  | Manager A  | 1          | 1     |
| 3  | Manager B  | 1          | 1     |
| 4  | Employee 1 | 2          | 2     |
| 5  | Employee 2 | 2          | 2     |
| 6  | Employee 3 | 3          | 2     |

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

CTE的优点

CTE具有以下优点：

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

CTE的缺点

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

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

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

Credits: Image by storyset