一句SQL完成動態分級查詢

http://www.cnblogs.com/powertoolsteam/p/sqlite.html
在最近的活字格項目中使用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 ALLSELECT [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 RECURSIVExaxis(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, yaxisUNION ALLSELECT 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;
復制代碼
執行結果（結果中的數字就是對應格子中的答案）

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

WITH

cte-table-name

Select-stmt:

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

附件：用到的SQL腳本
========

T-SQL 公用表表達式(CTE)

http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html


公用表表達式(CTE)
　　在編寫T-SQL代碼時，往往需要臨時存儲某些結果集。前面我們已經廣泛使用和介紹了兩種臨時存儲結果集的方法：臨時表和表變量。除此之外，還可以使用公用表表達式的方法。公用表表達式(Common Table Expression)是SQL Server2005版本的引入的一個特性。CTE可以看組是一個臨時的結果集，可以再接下來來的一個SELECT,INSERT,UPDATE,DELETE,MERGE語句中多次引用。使用公用表達式CTE可以讓語句更加清晰簡練。與公用表達式作用類似的還有臨時表和表變量。下面給出三種方法的對比。

　　1、3種方法比較

　　　(一)、臨時表：需要在臨時數據庫TempDB中通過I/O操作來創建表結構，一旦用戶推出SQL Server環境則自動被刪除。

　　　(二)、表變量：在內存中以表結構的形式存在，其定義與變量一致，其使用與表類似，不需要產生I/O。

　　　(三)、公用表表達式：Common Table Expression，定義在內存中保存的臨時存儲結果集對象，不產生I/O，不需要按照表變量這樣定義，使用方法和表類似。可以自己引用，也可以再查詢中被多次引用。

　　2、WITH AS的含義

　　WITH AS-做子查詢部分(subquery factoring)。

　　它用于定義一個SQL片段，該片段會被是整個SQL語句所用到。如果WITH AS所以定的表名被調用兩次以上，則優化器會自動將WITH AS所獲取的數據放入臨時表里，如果只是被調用一次，則不會。可以通過materialize將WITH AS短語里的數據強制放入全局臨時表里。

　　WITH AS可以被緊跟著的一條SQL語句所使用多次，但不能被緊跟著的多條SQL語句使用。

WITH B AS?
(
? ? SELECT * FROM xxx WHERE Id > 5
)
SELECT * FROM B
　　3、CTE的定義

　　　CTE的定義語法如下，主要包括3個部分。

　　　(一)、Expression_name:CTE表達式的名稱。

　　　(二)、Column_name:列名列表。

　　　(三)、CTE_query_definition:定義CTE結果集的Select查詢語句

　　WITH expression_name [(column_name [,...n] )]
　　AS
　　(?
　　　　cte_query_definition?
　　)
　　根據微軟對CTE好處的描述，可以歸結為四點:

　　可以定義遞歸公用表表達式(CTE)
　　當不需要將結果集作為視圖被多個地方引用時，CTE可以使其更加簡潔
　　GROUP BY語句可以直接作用于子查詢所得的標量列
　　可以在一個語句中多次引用公用表表達式(CTE)
　　按照是否遞歸，可以將公用表（CTE）表達式分為遞歸公用表表達式和非遞歸公用表表達式.

　　非遞歸公用表表達式(CTE)：

　　非遞歸公用表表達式（CTE）是查詢結果僅僅一次性返回一個結果集用于外部查詢調用。并不在其定義的語句中調用其自身的CTE。

　　非遞歸公用表表達式（CTE）的使用方式和視圖以及子查詢一致。

　　比如一個簡單的非遞歸公用表表達式:

WITH CTE_Test
AS
(
? ? SELECT * FROM Person_1
)
SELECT * FROM CTE_Test
　　公用表表達式的好處之一是可以在接下來一條語句中多次引用:

　　WITH CTE_TestAS(SELECT * FROM Person_1)SELECT * FROM CTE_Test AS a　　--第一次引用INNER JOIN CTE_Test AS b　　　 --第二次引用ON a.Id = b.IdORDER BY a.Id DESC

　　雖然以上引用了多次，但是只是一條語句，所以可以正常執行。

　　如果多條語句引用，如下面這樣，是會報錯的。

復制代碼
　　WITH CTE_Test
　　AS
　　(
? ? 　　SELECT * FROM Person_1
　　)
　　SELECT * FROM CTE_Test?


　　SELECT * FROM CTE_Test
復制代碼
　　輸出結果如下：
　　

　　由于CTE只能在接下來一條語句中使用，因此，當需要接下來的一條語句中引用多個CTE時，可以定義多個，中間用逗號分隔,下面是一次定義多個CTE的例子：

WITH CTE_Test1 AS ( SELECT * FROM Person_1 ), CTE_Test2 AS ( SELECT * FROM Person_2 ) SELECT * FROM CTE_Test1 UNION SELECT * FROM CTE_Test2

　　結果如下：
　　

　　　遞歸公用表表達式(CTE)：

? ?　　對于遞歸公用表達式來說，只需要在語句中定義兩部分：

? ?基本語句
? ?遞歸語句
? ?　　先建一張表欄目表如下，欄目Id，欄目名稱，欄目的父欄目。
　　　

　　現在使用CTE查詢其每個欄目是第幾層欄目的代碼如下：

WITH COL_CTE(Id,Name,ParentId,tLevel ) AS (--基本語句SELECT Id,Name,ParentId,0 AS tLevel FROM ColWHERE ParentId = 0UNION ALL--遞歸語句SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c INNER JOIN COL_CTE AS ce 　　--遞歸調用ON c.ParentId = ce.Id )

SELECT * FROM COL_CTE
復制代碼
　　輸出結果如下：
　　


　　0表示頂級欄目。1就是1級欄目。語法非常優雅。就一個SELECT * FRON COL_CTE。這正是CTE強大的地方，但是，這要有約束，否則如果無限制遞歸可以會消耗掉非常多的系統資源。下面來看看如何限制遞歸的最大次數。


　　如將上面的查詢語法改為：


復制代碼
WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
? ? --基本語句
? ? SELECT Id,Name,ParentId,0 AS tLevel FROM Col
? ? WHERE ParentId = 0
? ? UNION ALL
? ? --遞歸語句
? ? SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c?
? ? INNER JOIN COL_CTE AS ce?
? ? ON c.ParentId = ce.Id
)


SELECT * FROM COL_CTE
OPTION(MAXRECURSION 2)　　--指定最大遞歸次數為2
復制代碼
　　我們知道在上面的查詢中，要查到天河區新聞最少要遞歸3次，但是現在只遞歸2次，運行是什么結果呢？


　　
　　提示信息如下：


　　消息 530，級別 16，狀態 1，第 1 行
　　語句被終止。完成執行語句前已用完最大遞歸 2。
?　　CTE是一種十分優雅的存在。CTE所帶來最大的好處是代碼可讀性的提升,這是良好代碼的必須品質之一。使用遞歸CTE可以更加輕松愉快的用優雅簡潔的方式實現復雜的查詢。
========

T-SQL查詢進階--詳解公用表表達式(CTE)

簡介


? ? ?對于SELECT查詢語句來說,通常情況下，為了使T-SQL代碼更加簡潔和可讀，在一個查詢中引用另外的結果集都是通過視圖而不是子查詢來進行分解的.但是，視圖是作為系統對象存在數據庫中，那對于結果集僅僅需要在存儲過程或是用戶自定義函數中使用一次的時候，使用視圖就顯得有些奢侈了.


? ? 公用表表達式（Common Table Expression)是SQL SERVER 2005版本之后引入的一個特性.CTE可以看作是一個臨時的結果集，可以在接下來的一個SELECT,INSERT,UPDATE,DELETE,MERGE語句中被多次引用。使用公用表達式可以讓語句更加清晰簡練.


? ? ?除此之外，根據微軟對CTE好處的描述，可以歸結為四點:


? ? ?可以定義遞歸公用表表達式(CTE)
? ? ?當不需要將結果集作為視圖被多個地方引用時，CTE可以使其更加簡潔
? ? GROUP BY語句可以直接作用于子查詢所得的標量列
? ? 可以在一個語句中多次引用公用表表達式(CTE)
?


公用表表達式(CTE)的定義


? ? 公用表達式的定義非常簡單，只包含三部分：


? 公用表表達式的名字（在WITH之后）
? 所涉及的列名（可選）
? 一個SELECT語句(緊跟AS之后)
? ? 在MSDN中的原型：


WITH expression_name [ ( column_name [,...n] ) ]?


AS?


( CTE_query_definition )?
?


? ?按照是否遞歸，可以將公用表（CTE）表達式分為遞歸公用表表達式和非遞歸公用表表達式.


?


非遞歸公用表表達式(CTE)


? ?非遞歸公用表表達式（CTE）是查詢結果僅僅一次性返回一個結果集用于外部查詢調用。并不在其定義的語句中調用其自身的CTE


? ?非遞歸公用表表達式（CTE）的使用方式和視圖以及子查詢一致


? ?比如一個簡單的非遞歸公用表表達式:


? ?1


? ?當然，公用表表達式的好處之一是可以在接下來一條語句中多次引用:


? ?2


? ?前面我一直強調“在接下來的一條語句中”，意味著只能接下來一條使用:


? ?3


? ?由于CTE只能在接下來一條語句中使用，因此，當需要接下來的一條語句中引用多個CTE時，可以定義多個，中間用逗號分隔：


? ?4


遞歸公用表表達式(CTE)


? ? 遞歸公用表表達式很像派生表（Derived Tables ），指的是在CTE內的語句中調用其自身的CTE.與派生表不同的是，CTE可以在一次定義多次進行派生遞歸.對于遞歸的概念，是指一個函數或是過程直接或者間接的調用其自身，遞歸的簡單概念圖如下:


? ?1


? ? 遞歸在C語言中實現的一個典型例子是斐波那契數列:


long fib(int n) 　　
{ 　?
? ? ?if (n==0) return 0;
?　　if (n==1) return 1; 　　
? ? ?if (n>1) return fib(n-1)+fib(n-2);
}?
??


? ?上面C語言代碼可以看到，要構成遞歸函數，需要兩部分。第一部分是基礎部分，返回固定值，也就是告訴程序何時開始遞歸。第二部分是循環部分，是函數或過程直接或者間接調用自身進行遞歸.


? ?對于遞歸公用表達式來說，實現原理也是相同的，同樣需要在語句中定義兩部分：


? ?基本語句
? ?遞歸語句
? ?在SQL這兩部分通過UNION ALL連接結果集進行返回:


? ?比如：在AdventureWork中，我想知道每個員工所處的層級，0是最高級


? ?5


? ?這么復雜的查詢通過遞歸CTE變得如此優雅和簡潔.這也是CTE最強大的地方.


? ?當然，越強大的力量，就需要被約束.如果使用不當的話，遞歸CTE可能會出現無限遞歸。從而大量消耗SQL Server的服務器資源.因此，SQL Server提供了OPTION選項，可以設定最大的遞歸次數:


? ?還是上面那個語句，限制了遞歸次數:


? ?6


? ?所提示的消息:


? ?7


? ?這個最大遞歸次數往往是根據數據所代表的具體業務相關的，比如這里，假設公司層級最多只有2層.


總結?


? ? CTE是一種十分優雅的存在。CTE所帶來最大的好處是代碼可讀性的提升,這是良好代碼的必須品質之一。使用遞歸CTE可以更加輕松愉快的用優雅簡潔的方式實現復雜的查詢。
========

總結

以上是生活随笔為你收集整理的SQL CTE学习总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。