在上篇文章基礎上，我們進一步解決層級數據遞歸匯總問題 —— 讓上級部門的統計結果自動包含所有下級部門數據（含多級子部門），并新增請假天數大于 3 天的統計維度。通過遞歸 CTE、DECODE函數與分組函數的深度結合，實現真正意義上的樹形結構數據聚合。
一、業務需求升級：層級匯總與新增統計維度
核心目標
- 遞歸匯總：上級部門數據包含所有直屬 / 非直屬下級部門數據（如集團總部需匯總技術研發部、產品運營部及其子部門數據）
- 新增統計項：統計每個部門（含各級上級）的 "請假天數 > 3 天" 的記錄數
- 兼容原有指標：保留請假類型天數統計、狀態分類統計
數據模型擴展（無需修改表結構，新增計算邏輯）
-- 新增判斷邏輯：請假天數>3天標記DECODE(SIGN(leave_days - 3), 1, 1, 0) AS over_3_days_flag-- SIGN函數說明：返回1（正數）、0（零）、-1（負數），簡化條件判斷二、關鍵技術升級：雙向遞歸 CTE 構建層級關系
1. 遞歸 CTE 重構：獲取每個部門的所有后代部門
WITH dept_ancestor AS (-- 初始層：每個部門自身作為祖先SELECTdept_id,dept_name,parent_dept_id,dept_id AS ancestor_id -- 核心字段：標記當前部門的頂層祖先FROM t_deptUNION ALL-- 遞歸層：向下遍歷子部門，繼承祖先IDSELECTd.dept_id,d.dept_name,d.parent_dept_id,da.ancestor_id -- 子部門繼承父部門的祖先IDFROM t_dept dJOIN dept_ancestor da ON d.parent_dept_id = da.dept_id)- 核心邏輯：為每個部門生成從自身到所有后代的層級路徑，ancestor_id表示當前統計的頂層部門（如子部門 4 的 ancestor_id 可為自身 4、父部門 2、根部門 1）
- 遞歸方向：從父部門到子部門的向下遞歸，確保每個子部門關聯到所有上級祖先
2. 關聯請假表與遞歸 CTE
SELECTda.ancestor_id, -- 統計的目標部門（上級部門）da_dept.dept_name, -- 目標部門名稱tl.dept_id AS child_dept_id -- 實際產生數據的子部門ID（用于驗證層級）FROM dept_ancestor daLEFT 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的所有請假記錄三、DECODE 函數進階：多維度條件聚合
1. 新增 "請假 > 3 天" 統計
SUM(DECODE(SIGN(tl.leave_days - 3), 1, 1, 0)) AS over_3_days_count-- 等價于：SUM(CASE WHEN tl.leave_days > 3 THEN 1 ELSE 0 END)-- DECODE優勢：通過數值比較簡化條件表達式，執行效率更高2. 全維度統計表達式（整合新舊需求）
SELECTda_dept.dept_name AS 部門名稱,-- 請假類型統計（含下級部門）SUM(DECODE(tl.leave_type, '年假', tl.leave_days, 0)) AS 年假總天數,SUM(DECODE(tl.leave_type, '事假', tl.leave_days, 0)) AS 事假總天數,SUM(DECODE(tl.leave_type, '病假', tl.leave_days, 0)) AS 病假總天數,-- 狀態統計SUM(DECODE(tl.leave_status, '完成', 1, 0)) AS 完成請假數,SUM(DECODE(tl.leave_status, '進行中', 1, 0)) AS 進行中請假數,-- 新增統計：請假>3天SUM(DECODE(SIGN(tl.leave_days - 3), 1, 1, 0)) AS 超3天請假數四、終極 SQL：遞歸匯總全層級數據
完整實現代碼如下：
相信我，如果你能學會下面這個SQL的寫法或者能看懂，那么你對ORACLE類似邏輯的處理已經達到極高的水平，這個SQL我認為有95%的人會看不懂。如果你們都會了歡迎留言打臉。因為這個SQL的實現我在7年前專門拿出來給全公司技術人員進行過培訓，留了一個類似的作業，結果1個完成的都沒有。你也可以考慮下，如果不用SQL來實現，而是讓你去通過代碼去實現這個需求的統計，你需要寫多少代碼來實現，需要多少時間？
WITH dept_ancestor AS (-- 構建部門層級關系，獲取每個部門的所有祖先路徑SELECTdept_id,dept_name,parent_dept_id,dept_id AS ancestor_id -- 初始祖先為自身FROM t_deptUNION ALL-- 遞歸向下遍歷子部門，繼承祖先IDSELECTd.dept_id,d.dept_name,d.parent_dept_id,da.ancestor_id -- 子部門的祖先與父部門一致FROM t_dept dJOIN dept_ancestor da ON d.parent_dept_id = da.dept_id),-- 提取祖先部門的基礎信息（避免重復計算）ancestor_info AS (SELECT DISTINCT ancestor_id, dept_nameFROM dept_ancestor)SELECTai.dept_name AS 部門名稱,-- 請假類型匯總（含所有子部門）SUM(DECODE(tl.leave_type, '年假', tl.leave_days, 0)) AS 年假總天數,SUM(DECODE(tl.leave_type, '事假', tl.leave_days, 0)) AS 事假總天數,SUM(DECODE(tl.leave_type, '病假', tl.leave_days, 0)) AS 病假總天數,-- 狀態匯總SUM(DECODE(tl.leave_status, '完成', 1, 0)) AS 完成請假數,SUM(DECODE(tl.leave_status, '進行中', 1, 0)) AS 進行中請假數,-- 新增統計項SUM(DECODE(SIGN(tl.leave_days - 3), 1, 1, 0)) AS 超3天請假數FROM ancestor_info aiLEFT JOIN dept_ancestor da ON ai.ancestor_id = da.ancestor_idLEFT JOIN t_leave tl ON da.dept_id = tl.dept_id -- 關聯子部門請假數據GROUP BY ai.ancestor_id, ai.dept_nameORDER 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、DECODE 的多維應用：
????????類型統計：按leave_type分類累加天數
????????狀態統計：按leave_status分類計數
????????數值判斷：通過SIGN函數簡化 "大于 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. 大數據量處理
- 若部門層級超過 1000 層，需調整 Oracle 遞歸限制：
ALTER SESSION SET MAX_RECURSION_DEPTH = 2000; -- 默認1000層3. DECODE vs CASE WHEN 擴展
- 復雜范圍判斷（如BETWEEN）建議用CASE WHEN，等值判斷優先用DECODE
- 多層嵌套時注意DECODE的參數順序（嚴格按匹配順序執行）
七、總結：樹形數據統計的終極解決方案
通過遞歸 CTE 構建層級關系+DECODE 實現條件聚合+分組函數完成數據匯總，我們實現了：
????????1、真正的層級遞歸統計：上級部門自動包含所有下級數據，支持任意深度的組織架構
????????2、多維度復雜計算：在單個 SQL 中完成類型統計、狀態分類、數值判斷等多重邏輯
????????3、代碼極簡主義：相比傳統 Java 遞歸 + 多層循環，SQL 代碼量減少 90% 以上，且執行效率更高
????????這種方案特別適合組織架構復雜、層級統計頻繁的企業級應用（如人力資源管理、財務成本分攤等場景）。掌握遞歸與DECODE的組合使用，能讓你在處理樹形數據時如虎添翼，真正發揮 Oracle 數據庫的原生優勢。如果你能學會這種SQL邏輯，相信我，肯定會對你在實際工作中有巨大幫助。歡迎關注留言，期待與您一起進步。
