介绍
在本实验中，你将学习如何在 MySQL 中使用公共表表达式 (Common Table Expressions, CTEs)。CTEs 通过将复杂查询分解为逻辑清晰、易于阅读的步骤来简化它们。CTE 创建一个临时的、命名的结果集，你可以在单个 SQL 语句中引用它。
你将首先设置一个数据库，并创建一个简单的 CTE 来过滤员工数据。然后，你将探索递归 CTEs 来处理层级数据，例如组织结构图。最后，你将学习如何将 CTE 与表连接起来执行更高级的数据分析。
设置数据库和简单的 CTE
在第一步中，你将设置数据库环境并定义一个简单的公共表表达式 (CTE)。CTEs 使用 WITH 子句定义，并充当一个临时视图，仅在单个查询的持续时间内存在。这使得复杂查询更易于阅读和管理。
首先，从你的桌面打开终端。
以 root 用户连接到 MySQL 服务器。在此实验环境中，你可以使用 sudo 连接而无需密码。
sudo mysql -u root
连接成功后，你将看到 MySQL 提示符 (mysql>)。现在，创建一个名为 labex_db 的数据库并切换到它。
CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;
接下来，创建一个 employees 表并插入一些示例数据。此表将存储员工的基本信息，包括他们的部门和薪资。
CREATE TABLE IF NOT EXISTS employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);
现在表已准备就绪，让我们定义一个简单的 CTE 来仅选择 'Sales' 部门的员工。
WITH SalesEmployees AS (
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;
让我们分解一下这个查询：
WITH SalesEmployees AS (...): 这定义了一个名为SalesEmployees的 CTE。- 括号内的查询从
employees表中选择department为 'Sales' 的员工。 SELECT * FROM SalesEmployees;: 这是从我们临时的SalesEmployees结果集中检索所有数据的主查询。
你应该看到以下输出，仅列出了来自 Sales 部门的员工：
+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
| 3 | Robert | Jones | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)
这证实了你的第一个 CTE 工作正常。请保持在 MySQL shell 中进行下一步。
为层级数据创建递归 CTE
递归 CTEs 是查询层级数据（如组织结构图或嵌套类别）的强大功能。递归 CTE 通过自身引用来逐级处理树状结构。它包含一个“锚定成员”（起始点）和一个“递归成员”（迭代）。
在这一步中，你将创建一个表示员工层级的表，并使用递归 CTE 来显示它。
首先，在 MySQL shell 中，创建 employee_hierarchy 表。此表包含一个指向另一个员工 employee_id 的 manager_id，从而创建了层级结构。
CREATE TABLE IF NOT EXISTS employee_hierarchy (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
manager_id INT
);
INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);
在此结构中，'David' 是最高级别的经理，因为他的 manager_id 是 NULL。
现在，编写一个递归 CTE 来遍历此层级结构。需要使用 RECURSIVE 关键字。
WITH RECURSIVE EmployeeHierarchyCTE AS (
-- Anchor member: selects the top-level manager
SELECT employee_id, employee_name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: joins with itself to find subordinates
SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
FROM employee_hierarchy e
INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;
让我们分析一下这个查询：
- **锚定成员 (Anchor Member)**：第一个
SELECT语句查找层级的根（manager_id为NULL的地方），并为其分配level0。 - **递归成员 (Recursive Member)**：第二个
SELECT语句将employee_hierarchy与 CTE 本身 (EmployeeHierarchyCTE) 连接起来。它查找所有manager_id与 CTE 中已有的employee_id匹配的员工，并递增level。 - UNION ALL: 此运算符将锚定成员和递归成员的结果合并。
查询将产生以下输出，显示完整的组织结构图及其层级：
+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
| 1 | David | NULL | 0 |
| 2 | Emily | 1 | 1 |
| 3 | Frank | 1 | 1 |
| 4 | Grace | 2 | 2 |
| 5 | Henry | 2 | 2 |
| 6 | Ivy | 3 | 2 |
| 7 | Jack | 3 | 2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)
你已成功使用递归 CTE 查询了层级数据。请保持在 MySQL shell 中进行最后一步。
将 CTE 与表进行 JOIN 操作
CTEs 可以像普通表一样与其他表进行连接。这对于将 CTE 的聚合数据与另一个表的详细数据结合起来非常有用。在这一步中，你将创建一个 CTE 来计算每个部门的平均薪资，然后将其连接回 employees 表。
在 MySQL shell 中，执行以下查询。它定义了一个名为 AvgSalaryByDepartment 的 CTE，然后将其与 employees 表连接。
WITH AvgSalaryByDepartment AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
e.employee_id,
e.first_name,
e.department,
e.salary,
a.avg_salary
FROM
employees e
JOIN
AvgSalaryByDepartment a ON e.department = a.department;
以下是详细说明：
AvgSalaryByDepartmentCTE 计算每个部门的平均薪资。- 主查询然后将
employees表（别名为e）与此 CTE（别名为a）在department列上进行连接。 - 这使你能够显示每个员工的薪资及其部门的平均薪资。
预期的输出将是：
+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary | avg_salary |
+-------------+------------+------------+----------+--------------+
| 1 | John | Sales | 60000.00 | 57500.000000 |
| 2 | Jane | Marketing | 75000.00 | 72500.000000 |
| 3 | Robert | Sales | 55000.00 | 57500.000000 |
| 4 | Emily | IT | 90000.00 | 90000.000000 |
| 5 | Michael | Marketing | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)
你可以手动验证结果。例如，'Sales' 部门的平均薪资是 (60000 + 55000) / 2 = 57500，这与输出相符。这证实了你的查询工作正常。
你已成功将 CTE 与表连接。现在可以退出 MySQL shell。
exit;
总结
在此次实验中，你学习了如何在 MySQL 中有效地使用公共表表达式 (CTEs)。你从设置数据库和表开始，然后逐步创建了不同类型的 CTE。
你已学会：
- 使用
WITH子句定义一个简单的 CTE，用于过滤数据并提高查询的可读性。 - 创建一个递归 CTE 来导航和显示表中的层级数据。
- 将 CTE 与表连接，以将聚合结果与详细的行级数据结合起来，进行更复杂的分析。
这些技能对于编写清晰、可维护且强大的 SQL 查询至关重要，尤其是在处理复杂逻辑或数据结构时。
