MySQL CTE (Common Table Expressions)示例全解析

本文主要是介绍MySQL CTE (Common Table Expressions)示例全解析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQLCTE(CommonTableExpressions)示例全解析》MySQL8.0引入CTE,支持递归查询,可创建临时命名结果集,提升复杂查询的可读性与维护性,适用于层次结构数据处...

基本语法

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)示例全解析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！