SqlServer 2005 T-SQL Query 學習筆記（1)

2020-10-30 19:10:55

字體：大中小

來源：轉載

供稿：網友

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

SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM dbo.OrdersGROUP BY OrderYear;

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

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

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

還有一種很特殊的寫法：

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (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

(

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 NumCustsFROM CGROUP BY OrderYear;

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

WITH C(OrderYear, CustomerID) AS( SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP 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, NumCustsFROM C2WHERE 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 GrowthFROM 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;GOWITH 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, FaxINTO dbo.CustomersDupsFROM 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.VYearCntASWITH 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;