Select字句在邏輯上是SQL語句最后進(jìn)行處理的最后一步，所以，以下查詢會發(fā)生錯誤：　　

SELECT 
 
　　YEAR(OrderDate) AS OrderYear,  
 
　　COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM dbo.Orders  
 
　　GROUP BY OrderYear;  
 

　　因?yàn)間roup by是在Select之前進(jìn)行的，那個(gè)時(shí)候orderYear這個(gè)列并沒有形成。

　　如果要查詢成功，可以像下面進(jìn)行修改：

　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID  
 
　　FROM dbo.Orders) AS D  
 
　　GROUP BY OrderYear;  
 

　　還有一種很特殊的寫法：

　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM (SELECT YEAR(OrderDate), CustomerID  
 
　　FROM dbo.Orders) AS D(OrderYear, CustomerID)  
 
　　GROUP BY OrderYear;  
 

　　在作者眼里，他是非常喜歡這種寫法的，因?yàn)楦逦鞔_，更便于維護(hù)。

　　在查詢中使用參數(shù)定向產(chǎn)生一批結(jié)果，這個(gè)技巧沒有什么好說的。

　　嵌套查詢，在處理邏輯上是從里向外進(jìn)行執(zhí)行的。

　　多重引用，有可能你的SQL語句包含了多次從一個(gè)表進(jìn)行查詢后進(jìn)行連接組合。比如你要比較每年的顧客數(shù)同先前年的顧客數(shù)的變化，所以你的查詢就必須JOIN了2個(gè)相同的表的實(shí)例，這也是不可避免的。

　　Common Table Expressions (CTE)

　　CTE是在SQL2005新加入的一種表的表示類型。

　　它的定義如下：

　　WITH cte_name  
 
　　AS 
 
　　(  
 
　　cte_query  
 
　　)  
 
　　outer_query_refferring to_cte_name;  
 

　　注意：因?yàn)樵跇?biāo)準(zhǔn)的T-SQL語言中已經(jīng)包含了WITH關(guān)鍵字，所以為了區(qū)分，CTE在語句的結(jié)尾加上了“;”作為停止符。

　　CTE實(shí)例一(結(jié)果集別名)

　　WITH C AS 
 
　　(  
 
　　SELECT YEAR(OrderDate) AS OrderYear, CustomerID  
 
　　FROM dbo.Orders  
 
　　)  
 
　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM C  
 
　　GROUP BY OrderYear;  
 

　　當(dāng)然，作者本人有更推薦的寫法：

　　WITH C(OrderYear, CustomerID) AS 
 
　　(  
 
　　SELECT YEAR(OrderDate), CustomerID  
 
　　FROM dbo.Orders  
 
　　)  
 
　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM C  
 
　　GROUP BY OrderYear;  
 

　　CTE實(shí)例二(多重CTEs)

　　WITH C1 AS 
 
　　(  
 
　　SELECT YEAR(OrderDate) AS OrderYear, CustomerID  
 
　　FROM dbo.Orders  
 
　　),  
 
　　C2 AS 
 
　　(  
 
　　SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM C1  
 
　　GROUP BY OrderYear  
 
　　)  
 
　　SELECT OrderYear, NumCusts  
 
　　FROM C2  
 
　　WHERE NumCusts > 70;  
 

　　CTE實(shí)例叁(多重引用)

　　WITH YearlyCount AS 
 
　　(  
 
　　SELECT YEAR(OrderDate) AS OrderYear,  
 
　　COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM dbo.Orders  
 
　　GROUP BY YEAR(OrderDate)  
 
　　)  
 
　　SELECT Cur.OrderYear,  
 
　　Cur.NumCusts AS CurNumCusts, Prv.NumCusts AS PrvNumCusts,  
 
　　Cur.NumCusts - Prv.NumCusts AS Growth  
 
　　FROM YearlyCount AS Cur  
 
　　LEFT OUTER JOIN YearlyCount AS Prv  
 
　　ON Cur.OrderYear = Prv.OrderYear + 1;  
 

　　CTE實(shí)例四(修改數(shù)據(jù))

　　1.把從customer表查詢出來的結(jié)果，動態(tài)的組裝進(jìn)新表CustomersDups里：

　　IF OBJECT_ID('dbo.CustomersDups') IS NOT NULL 
 
　　DROP TABLE dbo.CustomersDups;  
 
　　GO  
 
　　WITH CrossCustomers AS 
 
　　(  
 
　　SELECT 1 AS c, C1.*  
 
　　FROM dbo.Customers AS C1, dbo.Customers AS C2  
 
　　)  
 
　　SELECT ROW_NUMBER() OVER(ORDER BY c) AS KeyCol,  
 
　　CustomerID, CompanyName, ContactName, ContactTitle, Address,  
 
　　City, Region, PostalCode, Country, Phone, Fax  
 
　　INTO dbo.CustomersDups  
 
　　FROM CrossCustomers;  
 

　　2.使用CTE移除數(shù)據(jù)，只保留CustomerDups表里同一CustomerID里KeyCol為最大的記錄。

　　WITH JustDups AS 
 
　　(  
 
　　SELECT * FROM dbo.CustomersDups AS C1  
 
　　WHERE KeyCol <  
 
　　(SELECT MAX(KeyCol) FROM dbo.CustomersDups AS C2  
 
　　WHERE C2.CustomerID = C1.CustomerID)  
 
　　)  
 
　　DELETE FROM JustDups;  
 

　　CTE實(shí)例五(對象容器)

　　即提供了封裝的能力，有利于組件化的編程。作者額外的提醒，CTE無法直接內(nèi)嵌，但是可以通過把CTE封裝進(jìn)一個(gè)對象容器里并從一個(gè)外部的CTE里對這容器的數(shù)據(jù)進(jìn)行查詢而實(shí)現(xiàn)內(nèi)嵌。

　　作者也說明了，使用CTEs在VIEW和UDFs里是沒有什么價(jià)值的。

　　有個(gè)例子，如下:

　　CREATE VIEW dbo.VYearCnt  
 
　　AS 
 
　　WITH YearCnt AS 
 
　　(  
 
　　SELECT YEAR(OrderDate) AS OrderYear,  
 
　　COUNT(DISTINCT CustomerID) AS NumCusts  
 
　　FROM dbo.Orders  
 
　　GROUP BY YEAR(OrderDate)  
 
　　)  
 
　　SELECT * FROM YearCnt;  
 

　　CTE實(shí)例六(CTEs的遞歸)

　　作者給了一個(gè)例子，來講述這個(gè)在SQL2005的新內(nèi)容，CTEs的遞歸。

　　根據(jù)employeeId，返回此員工的信息，并包含所有下級員工的信息。(等級關(guān)系基于empolyeeId和reportsTo的屬性)所返回的結(jié)果包含下列字段，employeeId,reportsTo,FirstName,LastName。

　　作者在這里，給予了一個(gè)最佳的索引方式：

　　CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname  
 
　　ON dbo.Employees(ReportsTo, EmployeeID)  
 
　　INCLUDE(FirstName, LastName);  
 

　　作者的解釋： 這個(gè)索引將通過一個(gè)單獨(dú)的查詢(局部掃描)來取得每個(gè)經(jīng)理的直接下級。Include(FristName,LastName)加在這里，即是覆蓋列。

　　小知識：什么Include索引?

　　Include索引是SQL2005的新功能。Include索引的列并不影響索引行的物理存儲順序，他們作為一個(gè)掛件‘掛在'索引行上。掛這些‘掛件'的目的在于，只需要掃描一把索引就獲得了這些附加數(shù)據(jù)。

　　回到作者的例子上，下面是遞歸的代碼：

　　WITH EmpsCTE AS 
 
　　(  
 
　　SELECT EmployeeID, ReportsTo, FirstName, LastName  
 
　　FROM dbo.Employees  
 
　　WHERE EmployeeID = 5  
 
　　UNION ALL 
 
　　SELECT EMP.EmployeeID, EMP.ReportsTo, EMP.FirstName, EMP.LastName  
 
　　FROM EmpsCTE AS MGR  
 
　　JOIN dbo.Employees AS EMP  
 
　　ON EMP.ReportsTo = MGR.EmployeeID  
 
　　)  
 
　　SELECT * FROM EmpsCTE;  
 

　　理解：一個(gè)遞歸的CTE包含了至少2個(gè)查詢，第一個(gè)查詢在CTE的身體里類似于一格錨點(diǎn)。這個(gè)錨點(diǎn)僅僅返回一個(gè)有效的表，并作為遞歸的一個(gè)錨。從上的例子看出來，錨點(diǎn)僅僅返回了一個(gè)employeeID = 5 的一行。然后的第2個(gè)查詢是作為遞歸成員。當(dāng)查詢到下屬成員的結(jié)果為空時(shí)，此遞歸結(jié)束。

　　如果你擔(dān)心遞歸會造成永久循環(huán)，你可以使用下面的表達(dá)：

　　WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n);  
 

　　默認(rèn)的n為100，當(dāng)n=0時(shí)，無限制。

原文出處：http://www.cnblogs.com/wwan/archive/2011/02/24/1964279.html