在《SQL Server 函数实战：一条 SQL 替代 3000 行代码的计算逻辑》基础上，我们进一步拓展业务需求，实现更复杂的层级数据统计。本次将重点解决两个核心问题：一是统计每个部门（含所有下级部门）请假天数大于 3 天的记录数量；二是让上级部门的统计结果自动汇总所有下级部门数据，实现树形结构的递归统计。通过递归 CTE、CASE WHEN函数与分组聚合的深度结合，完成从基础数据统计到层级化数据分析的跨越。
一、业务需求升级：层级汇总与新增统计维度
核心目标
- 递归汇总：上级部门的数据需包含直属及非直属下级部门的所有数据，例如集团总部要汇总技术研发部、产品运营部及其子部门的全部请假数据。
- 新增统计项：统计每个部门（含各级上级部门）请假天数大于 3 天的记录数量。
- 兼容原有指标：保留原有请假类型天数统计、请假状态分类统计等功能。
数据模型扩展（无需修改表结构，新增计算逻辑）
-- 新增判断逻辑：标记请假天数>3天的记录
CASE WHEN leave_days > 3 THEN 1 ELSE 0 END AS over_3_days_flag二、关键技术升级：双向递归 CTE 构建层级关系
1. 递归 CTE 重构：获取每个部门的所有后代部门
WITH dept_ancestor AS (
-- 初始层：每个部门自身作为祖先
SELECT
dept_id,
dept_name,
parent_dept_id,
dept_id AS ancestor_id -- 核心字段：标记当前部门的顶层祖先
FROM t_dept
UNION ALL
-- 递归层：向下遍历子部门，继承祖先ID
SELECT
d.dept_id,
d.dept_name,
d.parent_dept_id,
da.ancestor_id -- 子部门继承父部门的祖先ID
FROM t_dept d
JOIN dept_ancestor da ON d.parent_dept_id = da.dept_id
)- 核心逻辑：为每个部门生成从自身到所有后代的层级路径，ancestor_id表示当前统计的顶层部门（如子部门 4 的ancestor_id可为自身 4、父部门 2、根部门 1）。
- 递归方向：从父部门到子部门的向下递归，确保每个子部门关联到所有上级祖先。
2. 关联请假表与递归 CTE
三、CASE WHEN 函数进阶：多维度条件聚合
1. 新增 "请假 > 3 天" 统计
SELECT
da.ancestor_id, -- 统计的目标部门（上级部门）
da_dept.dept_name, -- 目标部门名称
tl.dept_id AS child_dept_id -- 实际产生数据的子部门ID（用于验证层级）
FROM dept_ancestor da
LEFT JOIN t_dept da_dept ON da.ancestor_id = da_dept.dept_id -- 关联祖先部门信息
LEFT JOIN t_leave tl ON da.dept_id = tl.dept_id -- 关联子部门请假数据
-- 示例输出：祖先部门1（集团总部）会关联到子部门2、3、4、5的所有请假记录SUM(CASE WHEN tl.leave_days > 3 THEN 1 ELSE 0 END) AS over_3_days_count此语句通过CASE WHEN判断请假天数是否大于 3 天，若满足条件则计数为 1，否则为 0，最后使用SUM函数汇总，实现对请假天数大于 3 天记录数量的统计。
2. 全维度统计表达式（整合新旧需求）
SELECT
da_dept.dept_name AS 部门名称,
-- 请假类型统计（含下级部门）
SUM(CASE tl.leave_type WHEN '年假' THEN tl.leave_days ELSE 0 END) AS 年假总天数,
SUM(CASE tl.leave_type WHEN '事假' THEN tl.leave_days ELSE 0 END) AS 事假总天数,
SUM(CASE tl.leave_type WHEN '病假' THEN tl.leave_days ELSE 0 END) AS 病假总天数,
-- 状态统计
SUM(CASE tl.leave_status WHEN '完成' THEN 1 ELSE 0 END) AS 完成请假数,
SUM(CASE tl.leave_status WHEN '进行中' THEN 1 ELSE 0 END) AS 进行中请假数,
-- 新增统计：请假>3天
SUM(CASE WHEN tl.leave_days > 3 THEN 1 ELSE 0 END) AS 超3天请假数四、终极 SQL：递归汇总全层级数据
完整实现代码
WITH dept_ancestor AS (
-- 构建部门层级关系，获取每个部门的所有祖先路径
SELECT
dept_id,
dept_name,
parent_dept_id,
dept_id AS ancestor_id -- 初始祖先为自身
FROM t_dept
UNION ALL
-- 递归向下遍历子部门，继承祖先ID
SELECT
d.dept_id,
d.dept_name,
d.parent_dept_id,
da.ancestor_id -- 子部门的祖先与父部门一致
FROM t_dept d
JOIN dept_ancestor da ON d.parent_dept_id = da.dept_id
),
-- 提取祖先部门的基础信息（避免重复计算）
ancestor_info AS (
SELECT DISTINCT ancestor_id, dept_name
FROM dept_ancestor
)
SELECT
ai.dept_name AS 部门名称,
-- 请假类型汇总（含所有子部门）
SUM(CASE tl.leave_type WHEN '年假' THEN tl.leave_days ELSE 0 END) AS 年假总天数,
SUM(CASE tl.leave_type WHEN '事假' THEN tl.leave_days ELSE 0 END) AS 事假总天数,
SUM(CASE tl.leave_type WHEN '病假' THEN tl.leave_days ELSE 0 END) AS 病假总天数,
-- 状态汇总
SUM(CASE tl.leave_status WHEN '完成' THEN 1 ELSE 0 END) AS 完成请假数,
SUM(CASE tl.leave_status WHEN '进行中' THEN 1 ELSE 0 END) AS 进行中请假数,
-- 新增统计项
SUM(CASE WHEN tl.leave_days > 3 THEN 1 ELSE 0 END) AS 超3天请假数
FROM ancestor_info ai
LEFT JOIN dept_ancestor da ON ai.ancestor_id = da.ancestor_id
LEFT JOIN t_leave tl ON da.dept_id = tl.dept_id -- 关联子部门请假数据
GROUP BY ai.ancestor_id, ai.dept_name
ORDER BY ai.ancestor_id;执行结果解析（新增示例数据后）
部门名称 |
年假总天数 |
事假总天数 |
病假总天数 |
完成请假数 |
进行中请假数 |
超 3 天请假数 |
集团总部 |
8.5 |
2.0 |
3.0 |
2 |
3 |
2 |
技术研发部 |
4.5 |
2.0 |
0.0 |
1 |
2 |
2 |
产品运营部 |
1.5 |
0.0 |
3.0 |
1 |
1 |
0 |
后端开发组 |
3.5 |
0.0 |
0.0 |
1 |
1 |
1 |
前端开发组 |
0.0 |
2.0 |
0.0 |
0 |
1 |
1 |
核心逻辑拆解
1、递归 CTE 双向关联：
向上：每个部门作为祖先，向下遍历所有子部门（ancestor_id固定为顶层部门）。
向下：通过da.dept_id = tl.dept_id关联子部门的实际数据，确保上级部门能获取所有下级数据。
2、CASE WHEN 的多维应用：
类型统计：按leave_type分类累加天数。
状态统计：按leave_status分类计数。
数值判断：通过CASE WHEN直接判断天数是否大于 3，简化条件转换。
3、分组策略：
按ancestor_id分组，确保每个上级部门汇总其所有后代（包括多级子部门）的数据。
LEFT JOIN确保无数据部门（如根部门若自身无数据）仍能显示统计结果。
五、与第一课的核心区别
特性 |
第一课（单部门统计） |
本文（递归层级统计） |
统计范围 |
仅当前部门或指定子部门 |
包含所有下级部门（多级递归） |
递归方向 |
单向向下（固定根部门） |
双向关联（每个部门可作为祖先） |
核心字段 |
dept_id直接分组 |
ancestor_id递归分组 |
新增功能 |
无 |
请假天数 > 3 天统计、层级汇总 |
六、性能优化与注意事项
1. 索引优化建议
-- 为部门层级关系创建索引
CREATE INDEX idx_dept_parent ON t_dept(parent_dept_id);
-- 为请假表关联字段创建索引
CREATE INDEX idx_leave_dept ON t_leave(dept_id);2. 大数据量处理
在 SQL Server 中，递归 CTE 默认有 100 层的递归限制，若部门层级较深，可通过以下方式调整：
-- 设置最大递归层数为200
OPTION (MAXRECURSION 200);3. CASE WHEN vs IIF 函数
- CASE WHEN支持复杂的条件判断和多分支逻辑，适用于大多数场景。
- IIF函数是CASE WHEN的简化写法，仅支持简单的二选一判断（如IIF(leave_days > 3, 1, 0)），在复杂逻辑中不适用。
七、总结：树形数据统计的进阶解决方案
通过递归 CTE 构建层级关系、CASE WHEN实现条件聚合、分组函数完成数据汇总，我们在 SQL Server 中实现了真正的层级递归统计。这种方案不仅能处理复杂的组织架构数据，还能灵活扩展统计维度，相比传统编程方式大幅减少代码量，提升开发效率与数据处理性能。后续我们还将继续探索更高级的统计分析功能，如果你有其他想实现的业务逻辑或优化需求，欢迎随时交流探讨。