CTE (Common Table Expression，公共表表达式) 是 MySQL 8.0 引入的重要特性，它允许在查询中创建临时命名结果集，提高复杂查询的可读性和可维护性。
基本语法
|
1
2
3
4
|
WITH cte_name AS (
SELECT ... -- CTE查询定义
)
SELECT * FROM cte_name; -- 主查询
|
CTE 主要特点
- 临时结果集：只在查询执行期间存在
- 可引用性：可以在主查询中多次引用
- 可读性强：比嵌套子查询更易理解
- 递归支持：支持递归查询（MySQL 8.0+）
非递归 CTE
简单 CTE 示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WITH department_stats AS (
SELECT
department_id,
COUNT(*) as employee_count,
AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
)
SELECT
d.department_name,
ds.employee_count,
ds.avg_salary
FROM departments d
JOIN department_stats ds ON d.department_id = ds.department_id;
|
多 CTE 示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
WITH
high_earners AS (
SELECT * FROM employees WHERE salary > 100000
),
it_employees AS (
SELECT * FROM employees WHERE department_id = 10
)
SELECT
h.employee_id,
h.name,
'High Earner' as category
FROM high_earners h
UNION ALL
SELECT
i.employee_id,
i.name,
'IT Employee' as category
FROM it_employees i;
|
递归 CTE
递归 CTE 可以处理层次结构数据，如组织结构、评论树等。
基本递归 CTE 结构
|
1
2
3
4
5
6
7
8
9
|
WITH RECURSIVE cte_name AS (
-- 基础部分（种子查询）
SELECT ... WHERE ...
UNION [ALL]
-- 递归部分
SELECT ... FROM cte_name JOIN ...
WHERE ...
)
SELECT * FROM cte_name;
|
递归 CTE 示例：组织结构查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
WITH RECURSIVE org_hierarchy AS (
-- 基础部分：查找顶级管理者
SELECT
employee_id,
name,
manager_id,
1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归部分：查找下属员工
SELECT
e.employee_id,
e.name,
e.manager_id,
oh.level + 1
FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy ORDER BY level, employee_id;
|
递归 CTE 示例：生成序列
|
1
2
3
4
5
6
|
WITH RECURSIVE number_sequence AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM number_sequence WHERE n < 10
)
SELECT * FROM number_sequence;
|
CTE 的优势
- 提高可读性：将复杂查询分解为逻辑块
- 避免重复：可以多次引用同一个CTE
- 替代视图：不需要创建永久视图
- 递归能力：处理层次结构数据
- 更好的优化：MySQL优化器能更好处理CTE
CTE 与派生表的比较
| 特性 |
CTE |
派生表 |
| 可读性 |
高 |
低 |
| 可重用性 |
可在查询中多次引用 |
每次使用都需要重新定义 |
| 递归支持 |
支持 |
不支持 |
| 性能 |
通常更好 |
可能较差 |
| 语法清晰度 |
更清晰 |
嵌套较深时难以理解 |
实际应用场景
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
WITH
monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') as month,
SUM(amount) as total_sales
FROM orders
GROUP BY month
),
growth_rate AS (
SELECT
month,
total_sales,
LAG(total_sales) OVER (ORDER BY month) as prev_sales,
(total_sales - LAG(total_sales) OVER (ORDER BY month)) /
LAG(total_sales) OVER (ORDER BY month) * 100 as growth_pct
FROM monthly_sales
)
SELECT * FROM growth_rate;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
|
WITH
raw_data AS (
SELECT * FROM source_table WHERE quality_check = 1
),
cleaned_data AS (
SELECT
id,
TRIM(name) as name,
CASE WHEN age < 0 THEN NULL ELSE age END as age
FROM raw_data
)
SELECT * FROM cleaned_data;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
WITH RECURSIVE path_finder AS (
SELECT
start_node as path,
start_node,
end_node,
1 as length
FROM graph
WHERE start_node = 'A'
UNION ALL
SELECT
CONCAT(pf.path, '->', g.end_node),
g.start_node,
g.end_node,
pf.length + 1
FROM graph g
JOIN path_finder pf ON g.start_node = pf.end_node
WHERE FIND_IN_SET(g.end_node, REPLACE(pf.path, '->', ',')) = 0
)
SELECT * FROM path_finder;
|
性能考虑
- 物化：MySQL可能会物化CTE结果
- 递归深度：默认递归深度限制为1000，可通过cte_max_recursion_depth参数调整
|
1
|
SET SESSION cte_max_recursion_depth = 2000;
|
|
1
2
3
4
|
WITH cte_name AS (
SELECT /*+ MERGE() */ * FROM table_name
)
SELECT * FROM cte_name;
|
限制
- MySQL 8.0 之前版本不支持CTE
- 某些复杂递归查询可能有性能问题
- 在存储过程和函数中使用有限制
CTE是MySQL中处理复杂查询的强大工具，合理使用可以显著提高SQL代码的可读性和维护性。
|
