✨SQL-递归CTE

📖 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};