一句SQL完成動態分級查詢

local0發表於2021-09-09

在最近的活字格專案中使用ActiveReports報表設計器設計一個報表模板時，遇到一個多級分類的難題：需要將某個部門所有銷售及下屬部門的銷售金額彙總，因為下屬級別的層次不確定，所以靠拼接子查詢的方式顯然是不能滿足要求，經過一番實驗，利用了CTE（Common Table Expression）很輕鬆解決了這個問題！

舉例：有如下的部門表

以及員工表

如果想查詢所有西北區的員工（包含西北、西安、蘭州），如下圖所示：

如何用CTE的方式實現呢？

Talk is cheap. Show me the code

-- 以下程式碼使用SQLite 3.18.0 測試通過 WITH [depts]([dept_id]) AS( SELECT [d].[dept_id] FROM [dept] [d] JOIN [employees] [e] ON [d].[dept_id] = [e].[dept_id] WHERE [e].[emp_name] = '西北-經理' UNION ALL SELECT [d].[dept_id] FROM [dept] [d] JOIN [depts] [s] ON [d].[parent_id] = [s].[dept_id] ) SELECT * FROM [employees] WHERE [dept_id] IN (SELECT [dept_id] FROM [depts]);

可能有些同學對CTE（Common Table Expression）還不太熟悉，這裡簡單說一下，有興趣的同學可以google或者百度，介紹很多（這裡以SQLite舉例）：

我還是更喜歡稱CTE（Common Table Expression）為“公用表變數”而不是“公用表示式”，因為從行為和使用場景上講，CTE更多的時候是產生（分迭代或者不迭代）結果集，供其後的語句使用（查詢、插入、刪除或更新），如上述的例子就是一個典型的利用迭代遍歷樹形結構資料。

CTE的優點：

遞迴的特點使得原本需要使用臨時表、儲存過程才能完成的邏輯，通過SQL就可以完成，尤其針對一些樹或者是圖的資料模型
因為是會話內的臨時結果集，不需要去顯示的宣告或銷燬
改寫後的SQL語句可讀性提高（看的明白才能修改）
給資料庫引擎優化執行計劃的可能性（這個不是肯定的，需要根據具體CTE的實現有關），優化了執行計劃，自然地效能就能上升

為了更好的說明CTE的能力，這裡附上兩個例子（轉自SQLite官網文件）

曼德勃羅集合（Mandelbrot set）

-- 以下程式碼使用SQLite 3.18.0 測試通過 WITH RECURSIVE
  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  m(iter, cx, cy, x, y) AS ( SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis UNION ALL SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m WHERE (x*x + y*y) < 4.0 AND iter<28 ),
  m2(iter, cx, cy) AS ( SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  ),
  a(t) AS ( SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') FROM m2 GROUP BY cy
  ) SELECT group_concat(rtrim(t),x'0a') FROM a;

執行後的結果，如下圖：（使用SQLite Expert Personal 4.2 x64）

數獨問題（Sudoku）

假設有類似下圖的問題：

-- 以下程式碼使用SQLite 3.18.0 測試通過 WITH RECURSIVE
  input(sud) AS ( VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
  ),
  digits(z, lp) AS ( VALUES('1', 1) UNION ALL SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9 ),
  x(s, ind) AS ( SELECT sud, instr(sud, '.') FROM input UNION ALL SELECT substr(s, 1, ind-1) || z || substr(s, ind+1),
      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' ) FROM x, digits AS z WHERE ind>0 AND NOT EXISTS ( SELECT 1 FROM digits AS lp WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1) OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1) OR z.z = substr(s, (((ind-1)/3) % 3) * 3 + ((ind-1)/27) * 27 + lp + ((lp-1) / 3) * 6, 1)
         )
  ) SELECT s FROM x WHERE ind=0;