您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 層次查詢實戰：用 CONNECT_BY_ROOT 實現樹狀匯總銷售統計

編輯：Oracle 教學

業務場景：統計所有父部門及其下屬節點的銷售總額

給定部門樹狀結構 (`DEPT` 表) 與銷售事實表 (`SALES`)，需求為：對每個部門，計算其本身及所有子孫部門的銷售額合計。傳統遞歸子查詢效能低下，Oracle 提供 `CONNECT_BY_ROOT` 運算子可高效解決。

測試資料建立

-- 部門表：遞歸關係 PARENT_DEPTNO -> DEPTNO，根節點 PARENT_DEPTNO = -1
CREATE TABLE dept (deptno NUMBER, deptname VARCHAR2(50), parent_deptno NUMBER);
INSERT INTO dept VALUES (1, '市場部', -1);
INSERT INTO dept VALUES (2, '市場一部', 1);
INSERT INTO dept VALUES (3, '市場二部', 1);
INSERT INTO dept VALUES (4, '銷售一組', 2);
INSERT INTO dept VALUES (5, '銷售二組', 2);
INSERT INTO dept VALUES (6, '銷售三組', 3);
INSERT INTO dept VALUES (7, '銷售四組', 3);
INSERT INTO dept VALUES (8, '電力1', 4);
INSERT INTO dept VALUES (9, '電力2', 5);
INSERT INTO dept VALUES (10, '電力3', 4);
INSERT INTO dept VALUES (11, '電力4', 5);
INSERT INTO dept VALUES (12, '石油1', 6);
INSERT INTO dept VALUES (13, '石油2', 7);
INSERT INTO dept VALUES (14, '石油3', 6);
INSERT INTO dept VALUES (15, '石油4', 7);
COMMIT;

-- 銷售表：僅葉子節點有銷售數據
CREATE TABLE sales (id NUMBER, sale_num NUMBER, deptno NUMBER);
INSERT INTO sales VALUES (1, 50000, 8);
INSERT INTO sales VALUES (2, 10000, 9);
INSERT INTO sales VALUES (3, 60000, 10);
INSERT INTO sales VALUES (4, 10000, 11);
INSERT INTO sales VALUES (5, 20000, 12);
INSERT INTO sales VALUES (6, 40000, 13);
INSERT INTO sales VALUES (7, 90000, 14);
INSERT INTO sales VALUES (8, 110000, 15);
COMMIT;

核心思路：CONNECT_BY_ROOT 標記根節點

`CONNECT_BY_ROOT(column)` 返回當前行在層次遍歷中的**根節點**該列的值。同一棵子樹的所有節點將擁有相同的 `root_id`，據此 `GROUP BY root_id` 即可完成匯總。

完整解決方案 SQL

WITH 
-- 1. 展開樹結構，標記每個節點的根部門
tree AS (
  SELECT d.deptno,
         CONNECT_BY_ROOT(d.deptno) AS root_id,
         ROWNUM AS rn
  FROM dept d
  START WITH d.parent_deptno = -1
  CONNECT BY PRIOR d.deptno = d.parent_deptno
),
-- 2. 關聯銷售數據，無銷售記錄補 0
sales_mapped AS (
  SELECT t.deptno, t.root_id, t.rn, NVL(s.sale_num, 0) AS sale_num
  FROM tree t
  LEFT JOIN sales s ON t.deptno = s.deptno
),
-- 3. 按根部門匯總銷售額
agg AS (
  SELECT root_id, SUM(sale_num) AS total_sales
  FROM sales_mapped
  GROUP BY root_id
),
-- 4. 取得部門顯示名稱（含縮排層級）
dept_display AS (
  SELECT d.deptno,
         CAST(LPAD(' ', (LEVEL - 1) * 2) || d.deptname AS VARCHAR2(50)) AS deptname,
         ROWNUM AS rn
  FROM dept d
  START WITH d.parent_deptno = -1
  CONNECT BY PRIOR d.deptno = d.parent_deptno
)
-- 5. 彙總輸出
SELECT dd.deptno, dd.deptname, a.total_sales
FROM agg a
JOIN dept_display dd ON a.root_id = dd.deptno
ORDER BY dd.rn;

執行結果

DEPTNO DEPTNAME                    TOTAL_SALES
---------- -------------------------------------------------- ----------
1 市場部                               390000
2   市場一部                           130000
4     銷售一組                         110000
8       電力1                           50000
10      電力3                           60000
5     銷售二組                          20000
9       電力2                           10000
11      電力4                           10000
3   市場二部                           260000
6     銷售三組                         110000
12      石油1                           20000
14      石油3                           90000
7     銷售四組                         150000
13      石油2                           40000
15      石油4                          110000

關鍵技術點解析

CONNECT_BY_ROOT： 僅在層次查詢 (`CONNECT BY`) 中可用，返回根節點列值，避免了傳統 `SYS_CONNECT_BY_PATH` 字串解析的開銷。
START WITH / CONNECT BY： 定義遍歷起點與遞歸關係，`PRIOR` 修飾父節點列。
CTE 分層處理： 結構清晰，便於除錯與維護；Oracle 12c+ 可考慮 `LATERAL` 視圖進一步優化。
效能優勢： 單次掃描完成樹展開與根標記，時間複雜度 O(N)，遠優於逐節點遞歸子查詢的 O(N²)。