📖 SQL魔法課堂：CTE「時間折疊術」全解
🎩 第一章：什么是CTE？
CTE（Common Table Expression） 就像 SQL 里的「臨時筆記本」📒：
WITH 臨時筆記本 AS ( SELECT ... FROM ... -- 先寫點筆記
)
SELECT * FROM 臨時筆記本; -- 再用筆記做分析
特點：
📌 臨時性：僅在當前查詢有效（像一次性草稿紙）
🧩 可復用：可在一個查詢中多次引用
🪄 自描述：增強SQL可讀性（比子查詢更清晰）
🌟 第二章：遞歸CTE——時間魔法師
當CTE學會「自我復制」，它就成了處理樹形結構、日期序列的利器！
🔮 經典結構：
WITH RECURSIVE 時間魔法師 AS (-- 🪄 初始咒語（錨點）SELECT 開始時間, 結束時間 FROM 時間表 WHERE...UNION ALL -- 連接符-- 🔄 遞歸咒語（時間+1天）SELECT 開始時間, 結束時間 + 1天 FROM 時間魔法師 WHERE 結束時間 < 目標時間
)
舉個栗子🌰：
把「2025-03-10 到 2025-03-12」的假期拆分成三天：
WITH RECURSIVE 拆分假期 AS (SELECT '2025-03-10' AS 假期日, '2025-03-12' AS 結束日UNION ALLSELECT 假期日 + 1 DAY, 結束日 FROM 拆分假期 WHERE 假期日 < 結束日
)
SELECT * FROM 拆分假期;
輸出結果：
| 假期日 | 結束日 |
|---|---|
| 2025-03-10 | 2025-03-12 |
| 2025-03-11 | 2025-03-12 |
| 2025-03-12 | 2025-03-12 |
🛠? 第三章：CTE實戰——假期拆分器
需求：把員工請假記錄按天展開，并關聯企業ID
WITH RECURSIVE 假期拆分器 AS (-- 🎯 錨點：獲取原始請假單SELECT vacation_id,emp_id,ent_id,DATE(start_time) AS 開始日,DATE(end_time) AS 結束日FROM vacation WHERE emp_id = 1001UNION ALL-- ? 遞歸：每天+1直到結束日SELECT vacation_id,emp_id,ent_id,開始日 + INTERVAL 1 DAY,結束日FROM 假期拆分器WHERE 開始日 < 結束日
)
SELECT ent_id,開始日 AS work_date,'holiday' AS type,vacation_id
FROM 假期拆分器
ORDER BY 開始日 DESC;
效果：
| ent_id | work_date | type | vacation_id |
|---|---|---|---|
| 1001 | 2025-03-12 | holiday | 202 |
| 1001 | 2025-03-11 | holiday | 202 |
| 1001 | 2025-03-10 | holiday | 202 |
?? 第四章：避坑指南
嚴格模式咬人🐞：
錯誤：1055 - Expression not in GROUP BY
解法：GROUP BY 必須包含所有非聚合字段
GROUP BY vacation_day, vacation_id, ent_id
遞歸深度限制：
默認最大遞歸100次，超長鏈需設置：
SET @@cte_max_recursion_depth = 365; -- 允許拆一年假期
性能優化：
📌 索引：vacation(emp_id, start_time, end_time)
🚫 避免大表遞歸：超過1萬行的遞歸可能變慢
💡 第五章：什么時候用CTE？
| 場景 | 優點 | 舉個栗 |
|---|---|---|
| 多層嵌套查詢 | 代碼更易讀 🧐 | 報表統計中的多步驟計算 |
| 遞歸結構處理 | 輕松拆解樹形數據 | 🌲 組織架構、日期序列 |
| 臨時結果復用 | 避免重復計算 | ? 多個JOIN用同一子查詢 |
? 總結：CTE的魔法三要素
清晰結構：WITH CTE名稱 AS (...) 像寫大綱
遞歸力量：UNION ALL + 終止條件 實現循環
嚴格模式生存法則：GROUP BY 要完整！
? DEMO：查詢假期分頁
WITH RECURSIVE vacation_days AS (SELECT vacation_id,emp_id,ent_id, -- 明確包含需要輸出的字段DATE(start_time) AS vacation_day,DATE(end_time) AS end_dayFROM vacation WHERE emp_id = #{emp_id}AND audit_status = 2AND del_flag = 0UNION ALLSELECT vacation_id,emp_id,ent_id, -- 遞歸時保留必要字段vacation_day + INTERVAL 1 DAY,end_dayFROM vacation_daysWHERE vacation_day < end_day
)
SELECT vd.ent_id,DATE_FORMAT(vd.vacation_day, '%Y-%m-%d') AS work_date,'holiday' AS type,vd.vacation_id
FROM vacation_days vd
-- 修正分組條件（添加ent_id保證GROUP BY完整性）
GROUP BY vd.vacation_day, vd.vacation_id, vd.ent_id
ORDER BY vd.vacation_day DESC
LIMIT #{pageSize} OFFSET #{offset};
