- 目錄
0. 背景說明
在編寫儲存過程的時候，大多數情況都會使用公用表表示式，本文對公用表表示式(CTE)作簡單的介紹
①準備工作
建立一個產品表T_Product
CREATE TABLE [dbo].[T_Product]
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,--自增欄位Id
[Category] [nvarchar](50) NOT NULL,--產品種類
[Name] [nvarchar](50) NOT NULL,--產品名
[Price] [decimal](18, 0) NOT NULL,--產品價格
)
隨便插入一些資料即可
②子表示式
瞭解公用表表示式之前我們要回顧一下SQL語句中的子查詢
之前在我們在select語句中可以使用子查詢，
例如：
select * from (select * from T_Product where Category='clothes' )T where T.Price>500`
這裡就是使用了一個子查詢:查詢出T_Product表中的所有的種類為clothes的產品，子查詢的結果表定義別名為T，我們在T表中繼續查詢所有Price大於500的產品
③表變數
子查詢雖然不復雜，但是使用多層的子查詢巢狀，則SQL語句會變得難以閱讀，
當然，我們可以使用表變數，即：定義一個表變數，然後將子查詢的結果集存入表變數中，以供後續的使用。
例如：
--宣告一個表變數
declare @T table
(
Id bigint not null ,
Category nvarchar(50) not null,
Name nvarchar(50) not nul ,
Prince decimal not null
)
--將子查詢的資料插入表變數中
insert into @T select * from T_Product where Category='clothes'
--使用表變數
select * from @T where Price>500
④公用表表示式
將子查詢放在了表變數@T中，這樣做將使SQL語句更容易維護，但又會帶來另一個問題，就是效能的損失。
由於表變數實際上使用了臨時表，從而增加了額外的I/O開銷，因此，表變數的方式並不太適合資料量大且頻繁查詢的情況。
為此，在SQL Server 2005中提供了另外一種解決方案，這就是公用表表示式（CTE），使用CTE，可以使SQL語句的可維護性，同時，CTE要比表變數的效率高得多。
這裡就先演示一下使用CTE，具體的語法細節後面做說明
--宣告一個CTE
with T as
(
select * from T_Product where Category='clothes'
)
--使用CTE
select * from T where Price >500
這裡我們就是使用公用表表示式替換這裡的子查詢的結果集。
所以使用使用with&as定義的公用表表示式也稱為子查詢部分（subquery factoring）
同時，公用表表示式在儲存過程中可以用於代替檢視的使用，在某個儲存過程中需要使用是個結果集，我們沒必要為其建立一個檢視，可以使用公用表表示式。
1. 定義及語法細節
1.1 基本定義
定義：公用表表示式(Common Table Expression) 是SQL Server2005版本的引入的一個特性。CTE可以看組是一個臨時的結果集，可以再接下來來的一個select,insert,update,delete,merge語句中多次引用。使用公用表示式CTE可以讓語句更加清晰簡練。
簡而言之：公用表表示式就是用於臨時儲存結果集。
1.2 基本語法
建立一個公用表表示式,並使用改公用表表示式的基本語法：
with subquery_name(column1，column2，column3……)--定義一個CTE
as
(
select column1，column2，column3 from table_name
)
select * from subquery_name-- 引用CTE
注意事項：
-
公用表表示式只能使用在其定義後的第一個sql語句中，否則會報錯
-
定義CTE的時候，可以省略列名，即最終的該CTE的列名就是該CTE中select查詢的結果，但是我現在覺得儘量不要省略，可以方便後續閱讀。
正確示例：
with temp as
(
select * from T_Product where Category='外套'
)
select * from temp as temp1 left join temp as temp2 on temp1.Id =temp2.Id
上面是宣告瞭一個公用表表示式temp,在其之後的第一句sql語句中使用到temp,完全沒有問題。
錯誤示例：
with temp as
(
select * from T_Product where Category='外套'
)
select * from temp --公用表表示式temp之後的第一句sql語句
select * from temp as temp1 left join temp as temp2 on temp1.Id =temp2.Id--公用表表示式temp之後的第二句sql語句
結果是報錯：顯示第一句sql語句已執行，但是第二句sql語句報錯“物件名'temp'無效”
正是因為只有CTE之後的第一句sql語句可以使用該CTE，所以如果CTE的表示式名稱與某個資料表或檢視重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE
CTE語法細節：
-
如果將 CTE 用在屬於批處理的一部分的語句中，那麼在CTE之前的語句必須以分號結尾。
【補充】：批處理是指從應用程式一次性地傳送一組完整sql語句到sql server上執行,批處理的所有語句被當做一個整體,被成批地分析,編譯和執行，所有的批處理 指令以
GO作為結束標誌。同時寫多個批處理，如果前面所有的批處理沒有問題，最後一個有錯誤那麼前面所有的批處理都不會執行 -
CTE中的sql語句是不能使用
order by語句，除非select語句中有top（其實這裡我也沒有想明白，理論上子查詢使用order by完全是沒有問題的） -
前面的with子句定義的查詢在後面的with子句中可以使用。但是一個with子句內部不能巢狀with子句
1.3 多個CTE同時宣告
這裡就要思考一個問題了，那就是若是在語句sql語句中需要多個公用表表示式，我們可以連續的宣告多個公用表表示式，但是注意只需要在第一個公用表表示式上使用with,之後相連的則不需在使用with
with temp1 as
(
select * from T_Product where Category='外套'
)
,temp2 as--注意這裡不在需要使用with ,但是不要忘記as
(
select * from T_Product where Category='褲子'
)
select * from temp1 ,temp2 where temp1.Price=temp2.Price
1.4 CTE巢狀使用
一次宣告的多個公用表表示式，後面的公用表表示式可以使用前面的公用表表示式
例如：
with temp1 as
(
select * from T_Product where Category='外套'
)
,temp2 as
(
select * from temp1 where Price>200--在相連的第二個CTE中使用第一個CTE
)
select * from temp2
2. CTE遞迴查詢
2.1 簡介
遞迴查詢主要用於層次結構的查詢，從葉級（Leaf Level）向頂層（Root Level）查詢，或從頂層向葉級查詢，或遞迴的路徑（Path）。
遞迴 CTE 定義至少必須包含兩個 CTE 查詢定義，一個定位點成員和一個遞迴成員。可以定義多個定位點成員和遞迴成員；但必須將所有定位點成員查詢定義置於第一個遞迴成員定義之前。
第一個子查詢稱作定點（Anchor）子查詢：定點查詢只是一個返回有效表的查詢，用於設定遞迴的初始值；
第二個子查詢稱作遞迴子查詢：該子查詢呼叫CTE名稱，觸發遞迴查詢，實際上是遞迴子查詢呼叫遞迴子查詢；
兩個子查詢使用union all，求並集；
2.2 準備工作
建立一個公司表，公司中的部門是分等級的，PId即該部門的上一級部門
CREATE TABLE [dbo].[Company]
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[PId] [bigint] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
)
插入資料,我們使用行政等級模擬上下級部門:
Id PId Name
--------- --------- ----------
1 0 中國
2 1 江蘇省
3 2 蘇州市
4 3 吳中區
5 1 山東省
6 5 濟南市
7 5 青島市
8 5 煙臺市
9 2 南京市
11 9 玄武區
2.3 計算每個部門的等級序列
USE [ShanTest]
GO
with temp as
(
select *,0 as Level from Company where Pid =0
union all
select c.Id,c.Pid,c.Name,temp.Level+1 as Level
from Company as c,temp where temp.Id=c.Pid
)
select * from temp
執行測試結果：
Id PId Name Level
--------- --------- ------------ ------------
1 0 中國 0
2 1 江蘇省 1
5 1 山東省 1
6 5 濟南市 2
7 5 青島市 2
8 5 煙臺市 2
3 2 蘇州市 2
9 2 南京市 2
11 9 玄武區 3
4 3 吳中區 3
簡單的理一理這裡的遞迴：
首先：
select *,0 as Level from Company where Pid =0
結果是：
Id PId Name Level
--------- --------- ------------ ------------
1 0 中國 0
接著：
union all
select c.Id,c.Pid,c.Name,temp.Level+1 as Level from Company as c,temp where c.Pid=temp .Id
第一次遞迴結果：
Id PId Name Level
--------- --------- ------------ ------------
1 0 中國 0
2 1 江蘇省 1
5 1 山東省 1
第二次遞迴結果：
Id PId Name Level
--------- --------- ------------ ------------
1 0 中國 0
2 1 江蘇省 1
5 1 山東省 1
6 5 濟南市 2
7 5 青島市 2
8 5 煙臺市 2
3 2 蘇州市 2
9 2 南京市 2
第三次遞迴結果：
Id PId Name Level
--------- --------- ------------ ------------
1 0 中國 0
2 1 江蘇省 1
5 1 山東省 1
6 5 濟南市 2
7 5 青島市 2
8 5 煙臺市 2
3 2 蘇州市 2
9 2 南京市 2
11 9 玄武區 3
4 3 吳中區 3
【注意】：
-
遞迴CTE可能會出現無限遞迴。從而大量消耗SQL Server的伺服器資源.因此，SQL Server提供了OPTION選項，可以設定最大的遞迴次數。
這個最大遞迴次數往往是根據資料所代表的具體業務相關的，比如這裡，我們定義最大遞迴數是2：
option(maxrecursion 2) -
遞迴查詢沒有顯式的遞迴終止條件，只有當遞迴子查詢返回空結果集（沒有資料行返回）或是超出了遞迴次數的最大限制時，才停止遞迴。
USE [ShanTest]
GO
with temp as
(
select *,0 as Level from Company where Pid =0
union all
select c.Id,c.Pid,c.Name,temp.Level+1 as Level
from Company as c,temp where c.Pid=temp .Id
)
select * from temp
option(maxrecursion 2)--設定最大遞迴數是2
則結果現實如下，即最大遞迴兩次則只能查詢到市一級，無法查詢到區一級
Id PId Name Level
--------- --------- ------------ ------------
1 0 中國 0
2 1 江蘇省 1
5 1 山東省 1
6 5 濟南市 2
7 5 青島市 2
8 5 煙臺市 2
3 2 蘇州市 2
9 2 南京市 2
訊息 530，級別 16，狀態 1，第 6 行
語句被終止。完成執行語句前已用完最大遞迴 2。
2.4 查詢所有的子級與父級匹配結果
作為層級結構，可以使用自連線查詢每個部門的上級部門：
--隱式內連線
select a.Id ,a.Pid ,a.Name ,b.Name as PName
from Company a ,Company b where a.Pid=b.Id
--顯式內連線：
select a.Id ,a.Pid ,a.Name ,b.Name as PName
from Company a inner join Company b on a.Pid =b.Id
查詢結果：
Id Pid Name PName
-------- -------- --------- ----------
2 1 江蘇省 中國
3 2 蘇州市 江蘇省
4 3 吳中區 蘇州市
5 1 山東省 中國
6 5 濟南市 山東省
7 5 青島市 山東省
8 5 煙臺市 山東省
9 2 南京市 江蘇省
11 9 玄武區 南京市
下面演示使用遞迴CTE實現，所有的子級匹配所有的父級
with subq as
(
select Id ,Pid ,Name ,Name as PName
from Company where Pid =0
union all
select c.Id ,c.Pid,c.Name ,s.Name as PName
from subq as s inner join Company as c on s.Id =c.Pid
--from subq as s,Company as c where s.Id=c.Pid
)
select * from subq
Id Pid Name PName
-------- -------- ---------- ---------
1 0 中國 中國
2 1 江蘇省 中國
5 1 山東省 中國
6 5 濟南市 山東省
7 5 青島市 山東省
8 5 煙臺市 山東省
3 2 蘇州市 江蘇省
9 2 南京市 江蘇省
11 9 玄武區 南京市
4 3 吳中區 蘇州市
理解遞迴的方式就是，從頭理一理：
首先：
select Id ,Pid ,Name ,Name as PName from Company where Pid =0
結果是：
Id Pid Name PName
-------- -------- ---------- ---------
1 0 中國 中國
接著
select c.Id ,c.Pid,c.Name ,s.Name as PName from subq as s inner join Company as c on s.Id =c.Pid
第一次遞迴結果：
Id Pid Name PName
-------- -------- ---------- ---------
1 0 中國 中國
2 1 江蘇省 中國
5 1 山東省 中國
第二次遞迴：
Id Pid Name PName
-------- -------- ---------- ---------
1 0 中國 中國
2 1 江蘇省 中國
5 1 山東省 中國
6 5 濟南市 山東省
7 5 青島市 山東省
8 5 煙臺市 山東省
3 2 蘇州市 江蘇省
9 2 南京市 江蘇省
第三次遞迴
Id Pid Name PName
-------- -------- ---------- ---------
1 0 中國 中國
2 1 江蘇省 中國
5 1 山東省 中國
6 5 濟南市 山東省
7 5 青島市 山東省
8 5 煙臺市 山東省
3 2 蘇州市 江蘇省
9 2 南京市 江蘇省
11 9 玄武區 南京市
4 3 吳中區 蘇州市
2.5 父查子-查詢某個部門的下級部門
比如說，這裡查詢表中所有江蘇省以下的行政區域
with temp as
(
select * from Company where Id=2--江蘇省的Id是2，所以遞迴初始值就是2
union all
select c.*
from temp ,szmCompany as c where temp.Id =c.Pid
)
select * from temp
--option(maxrecursion 1)
查詢結果：
Id Pid Name
------ ------ ----------
2 1 江蘇省
3 2 蘇州市
9 2 南京市
10 9 玄武區
4 3 吳中區
其實這裡，若是我們只需要江蘇省的下一級（即：市級），而不需要下下級（即：區縣級）
則可以設定遞迴的次數為1即可：option(maxrecursion 1)
結果為：
Id Pid Name
------ ------ ----------
2 1 江蘇省
3 2 蘇州市
9 2 南京市
訊息 530，級別 16，狀態 1，第 1 行
語句被終止。完成執行語句前已用完最大遞迴 1。
2.6 子查父-查詢某個下級部門的上級部門
通過子部門查詢其父部門，比如查詢吳中區的上級行政區域
with temp as
(
select * from Company where Id=4--吳中區Id
union all
select c.*
from temp ,Company as c where temp.Pid =c.Id
)
select * from temp
--option(maxrecursion 1)
查詢結果：
Id Pid Name
----- ----- --------
4 3 吳中區
3 2 蘇州市
2 1 江蘇省
1 0 中國
若是隻需要查詢吳中區的直系上級行政區域，則只要限制最大遞迴次數為1即可
當然，若是隻需要查直系上級，我們可以使用之前的上下級匹配的結果集，篩選特定的記錄：
select * from
(
select a.* ,b.Name as PName from Company as a ,Company as b where a.Pid=b.Id--所有的上下級匹配結果集
)X
where X.Id =4--吳中區Id
查詢結果：
Id Pid Name PName
------ ------- --------- ----------
4 3 吳中區 蘇州市
3. 參考
【待讀】：
想要找一本關於儲存過程的書籍，一直沒有找到，所以都是在網上的一些博文中學習相關的技巧和語法細節
感覺不繫統，隱隱約約感覺自己關於T-SQL以及儲存過程的使用還有許多不瞭解的地方！