SQL-递归CTE

2025-03-11 约 1046 字预计阅读 3 分钟

https://bing.ee123.net/img/rand?artid=146176461

✨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 ALL
    SELECT 假期日 + 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 = 1001
    
    UNION 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_day
    FROM vacation 
    WHERE 
        emp_id = #{emp_id}
        AND audit_status = 2
        AND del_flag = 0
    UNION ALL
    SELECT 
        vacation_id,
        emp_id,
        ent_id,  -- 递归时保留必要字段
        vacation_day + INTERVAL 1 DAY,
        end_day
    FROM vacation_days
    WHERE 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};