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

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

CTE語句結構

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

test4表見：SQL SERVER中apply操作符

使用上面的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自身(指Test_Recursion)，必須放在第一個遞迴行之上
    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