題外話： 以前也寫過幾篇，后來總算覺得寫得不夠好，寫了點(diǎn)又刪了點(diǎn)，最后一直沒有東西留下來，隨著時間的流逝，幾乎沒有積累。最近在看T-SQL相關(guān)的書，結(jié)合工作中遇到的問題，我打算寫點(diǎn)東西來記錄我的學(xué)習(xí)經(jīng)歷，希望大家指出我的不對的地方，希望一起探討開發(fā)中的問題。小弟再次謝過了。 “T-SQL相關(guān)的” T-SQL查詢處理詳解?
T-SQL查詢處理詳解 （續(xù)） T-SQL邏輯處理之表運(yùn)算符 廢話就不多說了，公用表表達(dá)式（Common Table Expressions）, 是SQL SERVER支持的一種類型的表表達(dá)式。 CTE的語法如下： WITH <cte_name> [(<target_col_list>)] AS ( <定義CTE的內(nèi)部查詢> ) <對CTE進(jìn)行外部查詢>; CTE的語法，從WITH開始，首先在括號里定義CTE內(nèi)部查詢，然后在外部查詢引用CTE的名稱。 對CTE的內(nèi)部查詢表達(dá)式，有如下規(guī)則： 1.查詢必須是一個有效的表； 2.所有的列必須要有名稱； 3.所有的列名必須唯一； 4.不允許使用order by（除非同時指定了top，原因很簡單，因?yàn)閛rder by之后，返回的是游標(biāo)，但是如果同時指定了top，則order by可以當(dāng)作是top的排序方式）。 下面是一個CTE的例子。 use Northwind;
go
with EmployeeYearOrder as
(
select (e.FirstName+N' '+e.LastName) as employeename,year(o.orderdate) as theyear,COUNT(o.OrderID) ordernum from dbo.Orders o
inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
group by e.FirstName+N' '+e.LastName,year(o.orderdate)
)
select * from EmployeeYearOrder cur
left join EmployeeYearOrder pre on cur.theyear = pre.theyear+1

上邊的例子是多引用的CTE，利用CTE定義了每年的雇員的訂單的數(shù)目，在外部查詢中，通過兩次引用EmployeeYearOrder ，一個代表當(dāng)前年份，一個代表上一年。

如果使用派生表的話，代碼是如下組織的：

use Northwind;
go
select * from
(
select (e.FirstName+N' '+e.LastName) as employeename,year(o.orderdate) as theyear,COUNT(o.OrderID) ordernum from dbo.Orders o
inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
group by e.FirstName+N' '+e.LastName,year(o.orderdate)
)as cur
left join
(
select (e.FirstName+N' '+e.LastName) as employeename,year(o.orderdate) as theyear,COUNT(o.OrderID) ordernum from dbo.Orders o
inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
group by e.FirstName+N' '+e.LastName,year(o.orderdate)
) as pre on cur.theyear = pre.theyear+1;

注意上邊的查詢，核心查詢的部分重復(fù)了兩次。查詢越復(fù)雜，引用次數(shù)越多，基于CTE的解決方案越有優(yōu)勢。當(dāng)然從性能上來講，這兩種寫法，經(jīng)過查詢優(yōu)化器分析后，最終都得到同樣的執(zhí)行計劃。

還有多CTE的情況。CTE不允許直接嵌套，但是可以用同一個WITH定義多個CTE，從而得到和嵌套派生表相同的效果，但是卻沒有嵌套派生表那么復(fù)雜。

看下邊的查詢：

多CTE的解決方案，返回的是每個雇員每一年處理的訂單的數(shù)目 with c1 as
(
select YEAR(orderdate) as theyear,(e.FirstName+N' '+e.LastName) as employeename,OrderID from dbo.Orders as o
inner join dbo.Employees e on o.EmployeeID = e.EmployeeID
),
c2 as
(
select theyear,employeename,COUNT(OrderID) ordernum from c1
group by theyear,employeename
)
select employeename,theyear,ordernum from c2;

就是一個多CTE的例子，看起來比多層嵌套的派生表要直觀。

CTE最大的用處，我覺得還是遞歸查詢。

還是給出一個例子。

WITH Emps AS
(
SELECT empid, mgrid, firstname, lastname
FROM HR.Employees
WHERE empid = 5

UNION ALL

SELECT Emp.empid, Emp.mgrid, Emp.firstname, Emp.lastname
FROM Emps AS Mgr
JOIN HR.Employees AS Emp
ON Emp.mgrid = Mgr.empid
)
SELECT * FROM Emps;

這段查詢將返回每位經(jīng)理的直接下屬。

如上查詢所示，遞歸的CTE，必須包含至少兩個查詢。第一個查詢被成為定位點(diǎn)成員，它只是一個返回有效表的查詢，作為遞歸的基礎(chǔ)或定位點(diǎn)。而第二個查詢則成為遞歸成員，是該查詢成為遞歸成員的是對CTE名稱的遞歸引用。如果擔(dān)心循環(huán)的發(fā)生，則可以指定option(maxrecursion n)來限制遞歸成員的調(diào)用次數(shù)。關(guān)于CTE的更多應(yīng)用和深入理解，有機(jī)會再深入去講解。

對于T-SQL公用表表達(dá)式，就講到這里，有什么問題，還請大家指出，一起探討。

參考文獻(xiàn)：《Microsoft SQL Server 2008 技術(shù)內(nèi)幕：T-SQL查詢》

轉(zhuǎn)載于:https://www.cnblogs.com/JimmyGe/archive/2011/03/12/1981998.html

總結(jié)

以上是生活随笔為你收集整理的T-SQL之公用表表达式(CTE)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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