SQLSERVER中CTE語句結構及CTE遞歸查詢

 　　SQL SERVER中CTE語句結構及CTE遞歸查詢

　　CTE語句結構

　　公用表表達式 (CTE) 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行范圍內定義的臨時結果集。CTE 與派生表類似，具體表現在不存儲為對象，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。

　　使用上面的test4表為例：

　　WITH TEST_CTE

　　AS

　　(

　　select * from test4

　　)

　　此句創建了名為TEST_CTE的select * from test4的結果集。由於它不存儲為對象，並且只在查詢期間有效，所CTE和查詢語句需要在一起執行：

　　WITH TEST_CTE

　　AS

　　(

　　select * from test4

　　)

　　select * from TEST_CTE

　　結果集跟select * from test4結果集機同。

　　下面是指定列的CTE使用：

　　WITH TEST_CTE(id)

　　AS

　　(

　　select id from test4

　　)

　　定義中的列需要與語句裡面的列對應，見紅色字體。

　　CTE可用於創建遞歸查詢。

　　創建測試表並插入數據：

　　create table test5

　　(

　　id int,

　　name varchar(50),

　　parentid int

　　)

　　insert into test5(id,name,parentid)

　　select 1,'父類1',0

　　union all

　　select 2,'父類2',0

　　union all

　　select 3,'父類3',0

　　union all

　　select 11,'子類11',1

　　union all

　　select 12,'子類12',1

　　union all

　　select 111,'子子類111',11

　　union all

　　select 22,'子類22',2

　　union all

　　select 222,'子子類222',22

　　結果：

　　id name parentid

　　1 父類1 0

　　2 父類2 0

　　3 父類3 0

　　11 子類11 1

　　12 子類12 1

　　111 子子類111 11

　　22 子類22 2

　　222 子子類222 22

　　使用CTE創建遞歸查詢，獲取父類1及所有其子類及子類的子類...：

　　with Test_Recursion(id,name,parentid,[level])

　　AS

　　(

　　select id,name,parentid,0 from test5 where id =1--沒有引用CTE自身必須放在第一個遞歸行之上

　　union all--沒有引用CTE自身的語句和第一個遞歸行之間必須用UNION ALL

　　select a.id,a.name,a.parentid,b.[level]+1 from test5 as a join Test_Recursion as b on a.parentid=b.id--遞歸行

　　)

　　select * from Test_Recursion

　　結果：

　　id name parentid level

　　1 父類1 0 0

　　11 子類11 1 1

　　12 子類12 1 1

　　111 子子類111 11 2