SQL Server 2005中有一種新的語法叫做通用表表達(dá)式，CTE（Common Table Expression）。

這種語法的好處就是可以創(chuàng)建出一張臨時(shí)的表，這張表可以在定義中使用自引用，使得我們處理父-子關(guān)系變得前所未有的方便.下面舉例子
說明之.
USE AdventureWorks
GO

WITH MyCTE( ListPrice, SellPrice) AS
(
? SELECT ListPrice, ListPrice * .95
? FROM Production.Product
)

SELECT *
FROM MyCTE
這里就是羅列兩列,將listprice*0.95.
當(dāng)然,要用臨時(shí)表的話也可以實(shí)現(xiàn),如下
CREATE TABLE #MyCTE
(
? ListPrice money,
? SellPrice money
)

INSERT INTO #MyCTE
(ListPrice, SellPrice)
SELECT ListPrice, ListPrice * .95
FROM Production.Product??

但要注意的是,在聲明CTE后,要立即調(diào)用,比如

USE AdventureWorks GO WITH MyCTE( ListPrice, SellPrice) AS ( SELECT ListPrice, ListPrice * .95 FROM Production.Product ) SELECT * FROM Production.Location SELECT * FROM MyCTE GO

將出錯(cuò)

??? CTE最大的用處就是用在遞歸查詢中了,CTE 引用它本身時(shí)，它被視為遞歸的。遞歸的 CTE 是根據(jù)至少兩個(gè)查詢（或者，用遞歸查詢的說法，為成員）構(gòu)建的。一個(gè)是非遞歸查詢，也稱為錨定成員 (AM)。另一個(gè)是遞歸查詢，也稱為遞歸成員 (RM)。查詢由 UNION ALL 運(yùn)算符分隔。以下示例顯示了遞歸 CTE 的簡化的一般形式：

WITH RecursiveCTE()
AS
(
? -- Anchor Member:
? -- SELECT query that does not refer to RecursiveCTE
? SELECT ...
? FROM
? ...
? UNION ALL
? -- Recursive Member
? -- SELECT query that refers to RecursiveCTE
? SELECT ...
? FROM
??? JOIN RecursiveCTE

? ...
)
-- Outer Query
SELECT ...
FROM RecursiveCTE
...

在邏輯上，您可以將實(shí)現(xiàn)遞歸 CTE 的算法視為：

1.
?錨定成員被激活。集 R0（R 表示“結(jié)果”）被生成。
?
2.
?遞歸成員被激活，在引用 RecursiveCTE 時(shí)獲得集 Ri（i = 步驟號(hào)）作為輸入。集 Ri + 1 被生成。
?
3.
?步驟 2 的邏輯被反復(fù)運(yùn)行（在每個(gè)迭代中遞增步驟號(hào)），直到返回空集。
?
4.
?外部查詢執(zhí)行，在引用 RecursiveCTE 時(shí)，獲得以前所有步驟的累積 (UNION ALL) 結(jié)果。?
?
舉例子說明:

USE tempdb -- or your own test database
CREATE TABLE Employees
(
? empid?? int???????? NOT NULL,
? mgrid?? int???????? NULL,
? empname varchar(25) NOT NULL,
? salary? money?????? NOT NULL,
? CONSTRAINT PK_Employees PRIMARY KEY(empid),
? CONSTRAINT FK_Employees_mgrid_empid
??? FOREIGN KEY(mgrid)
??? REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
SET NOCOUNT ON
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'?? , $10000.00)
INSERT INTO Employees VALUES(2 , 1?? , 'Andrew'? , $5000.00)
INSERT INTO Employees VALUES(3 , 1?? , 'Janet'?? , $5000.00)
INSERT INTO Employees VALUES(4 , 1?? , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2?? , 'Steven'? , $2500.00)
INSERT INTO Employees VALUES(6 , 2?? , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3?? , 'Robert'? , $2500.00)
INSERT INTO Employees VALUES(8 , 3?? , 'Laura'?? , $2500.00)
INSERT INTO Employees VALUES(9 , 3?? , 'Ann'???? , $2500.00)
INSERT INTO Employees VALUES(10, 4?? , 'Ina'???? , $2500.00)
INSERT INTO Employees VALUES(11, 7?? , 'David'?? , $2000.00)
INSERT INTO Employees VALUES(12, 7?? , 'Ron'???? , $2000.00)
INSERT INTO Employees VALUES(13, 7?? , 'Dan'???? , $2000.00)
INSERT INTO Employees VALUES(14, 11? , 'James'?? , $1500.00)

每個(gè)雇員都向其 ID 存儲(chǔ)在 mgrid 列中的經(jīng)理匯報(bào)。在引用 empid 列的 mgrid 列上定義了一個(gè)外鍵，這意味著經(jīng)理 ID 必須對(duì)應(yīng)于該表中的一個(gè)有效雇員 ID 或者為 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。
? 比如要顯示有關(guān) Robert (empid=7) 及其所有級(jí)別下屬的詳細(xì)信息。

WITH EmpCTE(empid, empname, mgrid, lvl)
AS
(

? -- Anchor Member (AM)
? SELECT empid, empname, mgrid, 0
? FROM Employees
? WHERE empid = 7
? UNION ALL
?
? -- Recursive Member (RM)
? SELECT E.empid, E.empname, E.mgrid, M.lvl+1
? FROM Employees AS E
??? JOIN EmpCTE AS M
????? ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE

empid?????? empname?????????????????? mgrid?????? lvl???????
----------- ------------------------- ----------- -----------
7?????????? Robert??????????????????? 3?????????? 0?????????
11????????? David???????????????????? 7?????????? 1?????????
12????????? Ron?????????????????????? 7?????????? 1?????????
13????????? Dan?????????????????????? 7?????????? 1?????????
14????????? James???????????????????? 11????????? 2????

lvl是遞歸的層數(shù),而且是可以控制的,比如

WITH EmpCTEJanet(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 3 UNION ALL SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees as E JOIN EmpCTEJanet as M ON E.mgrid = M.empid WHERE lvl < 2 ) SELECT empid, empname FROM EmpCTEJanet WHERE lvl = 2

以下為結(jié)果集：

empid empname ----------- ------------------------- 11 David 12 Ron 13 Dan

?

總結(jié)

以上是生活随笔為你收集整理的SQL SERVER 2005 中的CTE的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。