前言

本文將為開發(fā)者系統(tǒng)解析MySQL 8.0引入的CTE特性。通過真實業(yè)務(wù)場景案例，您將掌握：

1. 如何用CTE重構(gòu)嵌套噩夢般的SQL語句
2. 遞歸查詢實現(xiàn)樹形結(jié)構(gòu)遍歷的核心方法
3. 通過查詢復(fù)用提升30%以上執(zhí)行效率的技巧
4. CTE在復(fù)雜業(yè)務(wù)場景下的最佳實踐方案

1、成果收益

本最佳實踐已取得的成果或預(yù)期收益。

簡單說，簡化了復(fù)雜的SQL查詢，提升了復(fù)雜查詢的可讀性和復(fù)用性。

2、背景

講述一下問題或痛點，為什么要做這件事。

計費(fèi)規(guī)則根據(jù)險種進(jìn)行數(shù)據(jù)統(tǒng)計，計費(fèi)規(guī)則從地區(qū)到具體險種配置，地區(qū)-計費(fèi)-參保方案-參保方案詳情，層級深，關(guān)系一層套一層，很多表數(shù)據(jù)需要復(fù)用，只能反反復(fù)復(fù)的查詢。最后寫出一個層層嵌套的SQL，看也看不懂，改也改不動。

傳統(tǒng)方案面臨三大難題：

1. 嵌套黑洞：5層以上子查詢導(dǎo)致SQL可讀性斷崖式下降
2. 重復(fù)煉獄：相同子查詢在多個地方重復(fù)出現(xiàn)
3. 調(diào)試噩夢：修改一個字段需要追蹤多級嵌套

3、什么是CTE

公共表表達(dá)式（Common Table Expression，簡稱 CTE），CTE 是在SQL查詢中定義的一個臨時結(jié)果集，CTE 通常用于

• • 多層嵌套子查詢的簡化
  • 遞歸查詢（如樹形結(jié)構(gòu)遍歷，MySQL 8.0.1+支持遞歸CTE）
  • 多次復(fù)用同一子查詢結(jié)果

CTE 的定義部分類似于創(chuàng)建一個臨時視圖，但其生命周期僅限于當(dāng)前查詢。通過 WITH 創(chuàng)建臨時命名結(jié)果集，WITH 子句更輕量，適合在單個查詢中復(fù)用中間結(jié)果，提升復(fù)雜查詢的可讀性和復(fù)用性。

4、CTE如何使用

基本語法

WITH cte_name AS (
    -- 子查詢
    SELECT ...
)
SELECT ...
FROM cte_name;

• cte_name：CTE 的名稱，就是臨時表的名稱。
• 子查詢：定義 CTE 的結(jié)果集。
• 后續(xù)查詢：可以引用 CTE 名稱。

注意with是不需要分號結(jié)尾的，當(dāng)分號出現(xiàn)時就意味著當(dāng)前的SQL生命周期已經(jīng)結(jié)束了。

多個 CTE 的使用

可以在一個 WITH 子句中定義多個 CTE，用逗號分隔。

WITH 
cus_totals AS (
    SELECT cus_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY cus_id
),
latest_orders AS (
    SELECT cus_id, MAX(order_date) AS latest_date
    FROM orders
    GROUP BY cus_id
)
SELECT t.cus_id, t.total_amount, l.latest_date
FROM cus_totals t
JOIN latest_orders l ON t.cus_id = l.cus_id;

示例：

1. 第一個 CTE：計算每個客戶的訂單總金額。
2. 第二個 CTE：計算每個客戶的最新訂單日期。
3. 最終查詢：連接兩個 CTE，生成結(jié)果。

5、經(jīng)驗總結(jié)

主要用于數(shù)據(jù)導(dǎo)出的場景，更方便多表查詢的場景。

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

最新評論