SQLite中使用CTE巧解多級分類的級聯查詢

標籤：activereports   activereports報表設計師   cte   sqlite   

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

舉例：有如下的部門表

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110210319-109514451.png" style="border:0px;" />

以及員工表

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110218490-1500413472.png" style="border:0px;" />

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

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110226584-995996188.png" style="border:0px;" />

如何用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）

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110516819-2114551280.png" style="border:0px;" />

 

數獨問題（Sudoku）

假設有類似的問題：

 650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110532537-2050799675.png" style="border:0px;" />

-- 以下代碼使用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;

執行結果（結果中的數字就是對應格子中的答案）

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110613928-1307210909.png" style="border:0px;" />

附：SQLite中CTE（WITH關鍵字）文法圖解：

WITH

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110623569-798660279.gif" style="border:0px;" />

 

cte-table-name

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110631225-2120357525.gif" style="border:0px;" />

 

Select-stmt:

650) this.width=650;" src="http://images2015.cnblogs.com/blog/139239/201704/139239-20170424110646553-378105355.gif" style="border:0px;" />

 

總結

CTE是解決一些特定問題的利器，但瞭解和正確的使用是前提，在決定將已有的一些SQL重構為CTE之前，確保對已有語句有清晰的理解以及對CTE足夠的學習！Good Luck~~~

附件：用到的SQL指令碼

本文出自 “葡萄城控制項技術團隊部落格” 部落格，謝絕轉載！

SQLite中使用CTE巧解多級分類的級聯查詢