本文主要是介绍MySQL CTE (Common Table Expressions)示例全解析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
《MySQLCTE(CommonTableExpressions)示例全解析》MySQL8.0引入CTE,支持递归查询,可创建临时命名结果集,提升复杂查询的可读性与维护性,适用于层次结构数据处...
CTE (Common Tablejs Expression，公共表表达式) 是 mysql 8.0 引入的重要特性，它允许在查询中创建临时命名结果集，提高复杂查询的可读性和可维护性。
基本语法
WITH cte_name AS (
SELECT ... -- CTE查询定义
)
SELECT * FROM cte_name; -- 主查询CTE China编程主要特点
- 临时结果集：只在查询执行期间存在
- 可引用性：可以在主查询中多次引用
- 可读性强：比嵌套子查询更易理解
- 递归支持：支持递归查询（MySQL 8.0+）
非递归 CTE
简单 CTE 示例
WITH department_stats AS (
SELECT
department_id,
COUNT(*) as employee_count,
AVG(salary) as avg_javascriptsalary
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 示例
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 结构
WITH RECURSIVE cte_name AS (
-- 基础部分（种子查询）
SELECT ... WHERE ...
UNION [ALL]
-- 递归部分
SELECT ... FROM cte_name JOIN ...
WHERE ...
)
SELECT * FROM cte_name;递归 CTE 示例：组织结构查询
WITH RECURSIVE org_hierarchy AS (
-- 基础部分：查找顶级管理者
SELECT
employee_id,
name,
manager_id,
1 as level
FROM employees
WHERE manager_id IS NUphpLL
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 示例：生成序列
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 | 派生表 |
|---|---|---|
| 可读性 | 高 | 低 |
| 可重用性 | 可在查询中多次引用 | 每次使用都需要重新定义 |
| 递归支持 | 支持 | 不支持 |
| 性能 | 通常更好 | 可能较差 |
| 语法清晰度 | 更清晰 | 嵌套较深时难以理解 |
实际应用场景
- 数据报表：构建复杂报表的多步数据处理
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;- 数据清洗：多步数据转换
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;- 路径查找：图数android据查询
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参数调整
SET SESSION cte_max_recursion_depth = 2000;
- 优化器提示：可以使用提示影响CTE处理
WITH cte_name AS (
SELECT /*+ MERGE() */ * FROM table_name
)
SELECT * FROM cte_name;限制
- MySQL 8.0 之前版本不支持CTE
- 某些复杂递归查询可能有性能问题
- 在存储过程和函数中使用有限制
CTE是MySQL中处理复杂查询的强大工具，合理使用可以显著提高SQL代码的可读性和维护性。
到此这篇关于MySQL CTE (Common Table Expressions) 详解的文章就介绍到这了,更多相关mysql cte内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)！
这篇关于MySQL CTE (Common Table Expressions)示例全解析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
