公用表表達(dá)式 (CTE) 可以認(rèn)為是在單個(gè) SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語(yǔ)句的執(zhí)行范圍內(nèi)定義的臨時(shí)結(jié)果集。
CTE 與派生表類似，具體表現(xiàn)在不存儲(chǔ)為對(duì)象，并且只在查詢期間有效。
與派生表的不同之處在于，CTE 可自引用，還可在同一查詢中引用多次。
CTE可用于：
1.創(chuàng)建遞歸查詢(我個(gè)人認(rèn)為CTE最好用的地方)
2.在同一語(yǔ)句中多次引用生成的表
CTE優(yōu)點(diǎn):
使用 CTE 可以獲得提高可讀性和輕松維護(hù)復(fù)雜查詢的優(yōu)點(diǎn)。
查詢可以分為單獨(dú)塊、簡(jiǎn)單塊、邏輯生成塊。之后，這些簡(jiǎn)單塊可用于生成更復(fù)雜的臨時(shí) CTE，直到生成最終結(jié)果集。
CTE可使用的范圍：
可以在用戶定義的例程（如函數(shù)、存儲(chǔ)過(guò)程、觸發(fā)器或視圖）中定義 CTE。
下面看一個(gè)簡(jiǎn)單的CTE例題：
把test表中salary最大的id記錄保存在test_CTE中,再調(diào)用
復(fù)制代碼 代碼如下:
with test_CTE(id,salary)
as
(
select id ,max(salary)
from test
group by id
)
select * from test_cte
由上面例題可以看出：
CTE 由表示 CTE 的表達(dá)式名稱、可選列列表和定義 CET 的查詢組成。
定義 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 語(yǔ)句中對(duì)其進(jìn)行引用，就像引用表或視圖一樣。
簡(jiǎn)單的說(shuō)CTE可以替代臨時(shí)表和表變量的功能。
我個(gè)人認(rèn)為cte最好用的地方是創(chuàng)建遞歸查詢，下面演示一下這功能：
現(xiàn)有一數(shù)據(jù)結(jié)構(gòu)如下：
這些數(shù)據(jù)存放在表Co_ItemNameSet中,表結(jié)構(gòu)和部分?jǐn)?shù)據(jù)如下：
ItemId ParentItemId ItemName
2 0 管理費(fèi)用
3 0 銷售費(fèi)用
4 0 財(cái)務(wù)費(fèi)用
5 0 生產(chǎn)成本
35 5 材料
36 5 人工
37 5 制造費(fèi)用
38 35 原材料
39 35 主要材料
40 35 間輔材料
41 36 工資
42 36 福利
43 36 年獎(jiǎng)金
現(xiàn)在需求是:我想查詢ItemId=2,也就是管理費(fèi)用和其下屬所有節(jié)點(diǎn)的信息
通過(guò)CTE可以很簡(jiǎn)單達(dá)到需求要的數(shù)據(jù)
為了體現(xiàn)CTE的方便性，我特意也寫(xiě)了一個(gè)sql2000版本的解決方法，先看看sql2000是怎么解決這個(gè)問(wèn)題的
復(fù)制代碼 代碼如下:
--sql2000版本
DECLARE @i INT
SELECT @i=2;
/*
使用臨時(shí)表作為堆棧來(lái)跟蹤所有正在處理中的項(xiàng)目（已經(jīng)開(kāi)始但尚未結(jié)束）。
某個(gè)項(xiàng)目一旦處理完畢，將被從堆棧中刪除。
當(dāng)發(fā)現(xiàn)新的項(xiàng)目時(shí)，這些項(xiàng)目將被添加到堆棧中。
*/
CREATE TABLE #tem(
[ItemId] [INT] NOT NULL,
[level] INT
);
/*
存放結(jié)果
*/
CREATE TABLE #list(
[ItemId] [INT] NOT NULL,
[ParentItemId] [INT] NOT NULL DEFAULT ((0)),
[ItemName] [nvarchar](100) NOT NULL DEFAULT (''),
[level] INT
);
INSERT INTO #tem([ItemId],[level])
SELECT ItemId, 1
FROM Co_ItemNameSet
WHERE itemid=@i
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])
SELECT ItemId, ParentItemId, ItemName ,1
FROM Co_ItemNameSet
WHERE itemid=@i
DECLARE @level INT
SELECT @level=1
DECLARE @current INT
SELECT @current=0
/*
當(dāng) @level 大于 0 時(shí)，執(zhí)行以下步驟：
1.如果當(dāng)前級(jí)別 (@level) 的堆棧中有項(xiàng)目，就選擇其中一個(gè)，并稱之為 @current。
2.從堆棧中刪除該項(xiàng)目以免重復(fù)處理它，然后將其所有子項(xiàng)目添加到堆棧的下一級(jí) (@level + 1) 中。
3.如果有子項(xiàng)目 (IF @@ROWCOUNT > 0)，則下降一級(jí)處理它們 (@level = @level + 1)；否則，繼續(xù)在當(dāng)前級(jí)別上處理。
4.最后，如果在當(dāng)前級(jí)別的堆棧中沒(méi)有待處理的項(xiàng)目，則返回到上一級(jí)，看上一級(jí)是否有待處理的項(xiàng)目 (@level = @level - 1)。當(dāng)再?zèng)]有上一級(jí)時(shí)，則完畢。
*/
WHILE(@level>0)
BEGIN
SELECT @current=ItemId
FROM #tem
WHERE [level]=@level
IF @@ROWCOUNT>0
BEGIN
--從堆棧中刪除該項(xiàng)目以免重復(fù)處理它
DELETE FROM #tem
WHERE [level]=@level and ItemId=@current
--將其所有子項(xiàng)目添加到堆棧的下一級(jí) (@level + 1) 中。
INSERT INTO #tem([ItemId],[level])
SELECT [ItemId],@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current
--將其所有子項(xiàng)目添加
INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])
SELECT [ItemId],[ParentItemId],[ItemName] ,@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current
IF @@rowcount>0
BEGIN
SELECT @level=@level+1
END
END
ELSE
BEGIN
SELECT @level=@level-1
END
END
--顯示結(jié)果
SELECT * FROM #list
DROP TABLE #tem
DROP TABLE #list
go
結(jié)果如下：
ItemId ParentItemId ItemName level
2 0 管理費(fèi)用 1
52 2 汽車費(fèi)用 2
55 2 招聘費(fèi) 2
56 2 排污費(fèi) 2
53 52 燃料 3
54 52 輪胎 3
大家看到sql2000解決這個(gè)問(wèn)題比較麻煩，要實(shí)現(xiàn)這需求編寫(xiě)的代碼比較多，比較復(fù)雜
現(xiàn)在好了，在sql2005中通過(guò)CTE的遞歸特點(diǎn)可以2步就實(shí)現(xiàn).
得到同樣的結(jié)果，sql2005的CTE代碼簡(jiǎn)單了許多.這就是CTE支持遞歸查詢的魅力。
請(qǐng)看下面的代碼：
復(fù)制代碼 代碼如下:
--sql2005版本
DECLARE @i INT
SELECT @i=2;
WITH Co_ItemNameSet_CTE(ItemId, ParentItemId, ItemName,Level)
AS
(
SELECT ItemId, ParentItemId, ItemName ,1 AS [Level]
FROM Co_ItemNameSet
WHERE itemid=@i
UNION ALL
SELECT c.ItemId, c.ParentItemId, c.ItemName ,[Level] + 1
FROM Co_ItemNameSet c INNER JOIN Co_ItemNameSet_CTE ct
ON c.ParentItemId=ct.ItemId
)
SELECT * FROM Co_ItemNameSet_CTE
go
