程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL2005 學習筆記 公用表表達式(CTE)

SQL2005 學習筆記 公用表表達式(CTE)

編輯:關於SqlServer
公用表表達式 (CTE) 可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行范圍內定義的臨時結果集。
CTE 與派生表類似,具體表現在不存儲為對象,並且只在查詢期間有效。
與派生表的不同之處在於,CTE 可自引用,還可在同一查詢中引用多次。
CTE可用於:
1.創建遞歸查詢(我個人認為CTE最好用的地方)
2.在同一語句中多次引用生成的表
CTE優點:
使用 CTE 可以獲得提高可讀性和輕松維護復雜查詢的優點。
查詢可以分為單獨塊、簡單塊、邏輯生成塊。之後,這些簡單塊可用於生成更復雜的臨時 CTE,直到生成最終結果集。
CTE可使用的范圍:
可以在用戶定義的例程(如函數、存儲過程、觸發器或視圖)中定義 CTE。
下面看一個簡單的CTE例題:
把test表中salary最大的id記錄保存在test_CTE中,再調用
代碼如下:

with test_CTE(id,salary)
as
(
select id ,max(salary)
from test
group by id
)
select * from test_cte

由上面例題可以看出:
CTE 由表示 CTE 的表達式名稱、可選列列表和定義 CET 的查詢組成。
定義 CTE 後,可以在 SELECT、INSERT、UPDATE 或 DELETE 語句中對其進行引用,就像引用表或視圖一樣。
簡單的說CTE可以替代臨時表和表變量的功能。
我個人認為cte最好用的地方是創建遞歸查詢,下面演示一下這功能:
現有一數據結構如下:
QQ截圖未命名1.bmp
這些數據存放在表Co_ItemNameSet中,表結構和部分數據如下:
ItemId ParentItemId ItemName
2 0 管理費用
3 0 銷售費用
4 0 財務費用
5 0 生產成本
35 5 材料
36 5 人工
37 5 制造費用
38 35 原材料
39 35 主要材料
40 35 間輔材料
41 36 工資
42 36 福利
43 36 年獎金

現在需求是:我想查詢ItemId=2,也就是管理費用和其下屬所有節點的信息
通過CTE可以很簡單達到需求要的數據
為了體現CTE的方便性,我特意也寫了一個sql2000版本的解決方法,先看看sql2000是怎麼解決這個問題的
代碼如下:

--sql2000版本
DECLARE @i INT
SELECT @i=2;

/*
使用臨時表作為堆棧來跟蹤所有正在處理中的項目(已經開始但尚未結束)。
某個項目一旦處理完畢,將被從堆棧中刪除。
當發現新的項目時,這些項目將被添加到堆棧中。
*/
CREATE TABLE #tem(
[ItemId] [INT] NOT NULL,
[level] INT
);

/*
存放結果
*/
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

/*
當 @level 大於 0 時,執行以下步驟:
1.如果當前級別 (@level) 的堆棧中有項目,就選擇其中一個,並稱之為 @current。
2.從堆棧中刪除該項目以免重復處理它,然後將其所有子項目添加到堆棧的下一級 (@level + 1) 中。
3.如果有子項目 (IF @@ROWCOUNT > 0),則下降一級處理它們 (@level = @level + 1);否則,繼續在當前級別上處理。
4.最後,如果在當前級別的堆棧中沒有待處理的項目,則返回到上一級,看上一級是否有待處理的項目 (@level = @level - 1)。當再沒有上一級時,則完畢。
*/
WHILE(@level>0)
BEGIN
SELECT @current=ItemId
FROM #tem
WHERE [level]=@level

IF @@ROWCOUNT>0
BEGIN
--從堆棧中刪除該項目以免重復處理它
DELETE FROM #tem
WHERE [level]=@level and ItemId=@current

--將其所有子項目添加到堆棧的下一級 (@level + 1) 中。
INSERT INTO #tem([ItemId],[level])
SELECT [ItemId],@level+1
FROM Co_ItemNameSet
WHERE ParentItemId=@current

--將其所有子項目添加
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

--顯示結果
SELECT * FROM #list

DROP TABLE #tem
DROP TABLE #list
go

結果如下:
ItemId ParentItemId ItemName level
2 0 管理費用 1
52 2 汽車費用 2
55 2 招聘費 2
56 2 排污費 2
53 52 燃料 3
54 52 輪胎 3

大家看到sql2000解決這個問題比較麻煩,要實現這需求編寫的代碼比較多,比較復雜
現在好了,在sql2005中通過CTE的遞歸特點可以2步就實現.
得到同樣的結果,sql2005的CTE代碼簡單了許多.這就是CTE支持遞歸查詢的魅力。
請看下面的代碼:
代碼如下:

--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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved