快捷導(dǎo)航

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

更新時(shí)間：2025年07月26日 10:36:54 作者：Full Stack Developme

MySQL 8.0引入CTE,支持遞歸查詢,可創(chuàng)建臨時(shí)命名結(jié)果集,提升復(fù)雜查詢的可讀性與維護(hù)性,適用于層次結(jié)構(gòu)數(shù)據(jù)處理,但需注意性能和遞歸深度限制,本文給大家介紹MySQL CTE (Common Table Expressions)示例,感興趣的朋友一起看看吧

基本語(yǔ)法

WITH cte_name AS (
    SELECT ...  -- CTE查詢定義
)
SELECT * FROM cte_name;  -- 主查詢

CTE 主要特點(diǎn)

臨時(shí)結(jié)果集：只在查詢執(zhí)行期間存在
可引用性：可以在主查詢中多次引用
可讀性強(qiáng)：比嵌套子查詢更易理解
遞歸支持：支持遞歸查詢（MySQL 8.0+）

非遞歸 CTE

簡(jiǎn)單 CTE 示例

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 示例

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 可以處理層次結(jié)構(gòu)數(shù)據(jù)，如組織結(jié)構(gòu)、評(píng)論樹等。

基本遞歸 CTE 結(jié)構(gòu)

WITH RECURSIVE cte_name AS (
    -- 基礎(chǔ)部分（種子查詢）
    SELECT ... WHERE ...
    UNION [ALL]
    -- 遞歸部分
    SELECT ... FROM cte_name JOIN ...
    WHERE ...
)
SELECT * FROM cte_name;

遞歸 CTE 示例：組織結(jié)構(gòu)查詢

WITH RECURSIVE org_hierarchy AS (
    -- 基礎(chǔ)部分：查找頂級(jí)管理者
    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 示例：生成序列

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 的優(yōu)勢(shì)

提高可讀性：將復(fù)雜查詢分解為邏輯塊
避免重復(fù)：可以多次引用同一個(gè)CTE
替代視圖：不需要?jiǎng)?chuàng)建永久視圖
遞歸能力：處理層次結(jié)構(gòu)數(shù)據(jù)
更好的優(yōu)化：MySQL優(yōu)化器能更好處理CTE

CTE 與派生表的比較

特性	CTE	派生表
可讀性	高	低
可重用性	可在查詢中多次引用	每次使用都需要重新定義
遞歸支持	支持	不支持
性能	通常更好	可能較差
語(yǔ)法清晰度	更清晰	嵌套較深時(shí)難以理解

實(shí)際應(yīng)用場(chǎng)景

數(shù)據(jù)報(bào)表：構(gòu)建復(fù)雜報(bào)表的多步數(shù)據(jù)處理

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;

數(shù)據(jù)清洗：多步數(shù)據(jù)轉(zhuǎn)換

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;

路徑查找：圖數(shù)據(jù)查詢

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可能會(huì)物化CTE結(jié)果
遞歸深度：默認(rèn)遞歸深度限制為1000，可通過cte_max_recursion_depth參數(shù)調(diào)整

SET SESSION cte_max_recursion_depth = 2000;

優(yōu)化器提示：可以使用提示影響CTE處理

WITH cte_name AS (
    SELECT /*+ MERGE() */ * FROM table_name
)
SELECT * FROM cte_name;

限制

MySQL 8.0 之前版本不支持CTE
某些復(fù)雜遞歸查詢可能有性能問題
在存儲(chǔ)過程和函數(shù)中使用有限制

CTE是MySQL中處理復(fù)雜查詢的強(qiáng)大工具，合理使用可以顯著提高SQL代碼的可讀性和維護(hù)性。

到此這篇關(guān)于MySQL CTE (Common Table Expressions) 詳解的文章就介紹到這了,更多相關(guān)mysql cte內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片