T-SQL WITH AS

T-SQL with關鍵字

Select字句在邏輯上是SQL語句最後進行處理的最後一步，是以，以下查詢會發生錯誤：

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

因為group by是在Select之前進行的，那個時候orderYear這個列并沒有形成。

如果要查詢成功，可以像下面進行修改：

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;      

在作者眼裡，他是非常喜歡這種寫法的，因為更清晰，更明确，更便于維護。

在查詢中使用參數定向産生一批結果，這個技巧沒有什麼好說的。

嵌套查詢，在處理邏輯上是從裡向外進行執行的。

多重引用，有可能你的SQL語句包含了多次從一個表進行查詢後進行連接配接組合。比如你要比較每年的顧客數同先前年的顧客數的變化，是以你的查詢就必須JOIN了2個相同的表的執行個體，這也是不可避免的。

Common Table Expressions (CTE)

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

它的定義如下：

WITH cte_name

AS

(

cte_query

)

outer_query_refferring to_cte_name;

注意：因為在标準的T-SQL語言中已經包含了WITH關鍵字，是以為了區分，CTE在語句的結尾加上了“;”作為停止符。

CTE執行個體一（結果集别名）

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

當然，作者本人有更推薦的寫法：

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執行個體二（多重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執行個體三（多重引用）

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執行個體四（修改資料）

1.把從customer表查詢出來的結果，動态的組裝進新表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移除資料，隻保留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執行個體五（對象容器）

即提供了封裝的能力，有利于元件化的程式設計。作者額外的提醒，CTE無法直接内嵌，但是可以通過把CTE封裝進一個對象容器裡并從一個外部的CTE裡對這容器的資料進行查詢而實作内嵌。

作者也說明了，使用CTEs在VIEW和UDFs裡是沒有什麼價值的。

有個例子，如下:

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執行個體六（CTEs的遞歸）

作者給了一個例子，來講述這個在SQL2005的新内容，CTEs的遞歸。

根據employeeId，傳回此員工的資訊，并包含所有下級員工的資訊。（等級關系基于empolyeeId和reportsTo的屬性）所傳回的結果包含下列字段，employeeId,reportsTo,FirstName,LastName。

作者在這裡，給予了一個最佳的索引方式：

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

作者的解釋： 這個索引将通過一個單獨的查詢（局部掃描）來取得每個經理的直接下級。Include（FristName,LastName）加在這裡，即是覆寫列。

小知識：什麼Include索引？

Include索引是SQL2005的新功能。Include索引的列并不影響索引行的實體存儲順序，他們作為一個挂件‘挂在'索引行上。挂這些‘挂件'的目的在于，隻需要掃描一把索引就獲得了這些附加資料。

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

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;      

了解：一個遞歸的CTE包含了至少2個查詢，第一個查詢在CTE的身體裡類似于一格錨點。這個錨點僅僅傳回一個有效的表，并作為遞歸的一個錨。從上的例子看出來，錨點僅僅傳回了一個employeeID = 5 的一行。然後的第2個查詢是作為遞歸成員。當查詢到下屬成員的結果為空時，此遞歸結束。

如果你擔心遞歸會造成永久循環，你可以使用下面的表達：

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

預設的n為100，當n=0時，無限制。