在最近的活字格專案中使用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
執行後的結果，如下圖：（使用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 lp0      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;
執行結果（結果中的數字就是對應格子中的答案）
附：SQLite中CTE（WITH關鍵字）語法圖解：
WITH
 
cte-table-name
 
Select-stmt:
 
總結
CTE是解決一些特定問題的利器，但瞭解和正確的使用是前提，在決定將已有的一些SQL重構為CTE之前，確保對已有語句有清晰的理解以及對CTE足夠的學習！Good Luck~~~