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時,無限制。