Select字句在邏輯上是SQL語(yǔ)句最后進(jìn)行處理的最后一步,所以,以下查詢會(huì)發(fā)生錯(cuò)誤:
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è)列并沒(méi)有形成。
如果要查詢成功,可以像下面進(jìn)行修改:
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID
FROM dbo.Orders) AS D
GROUP BY OrderYear;
還有一種很特殊的寫(xiě)法:
SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts
FROM (SELECT YEAR(OrderDate), CustomerID
FROM dbo.Orders) AS D(OrderYear, CustomerID)
GROUP BY OrderYear;
在作者眼里,他是非常喜歡這種寫(xiě)法的,因?yàn)楦逦鞔_,更便于維護(hù)。
在查詢中使用參數(shù)定向產(chǎn)生一批結(jié)果,這個(gè)技巧沒(méi)有什么好說(shuō)的。
嵌套查詢,在處理邏輯上是從里向外進(jìn)行執(zhí)行的。
多重引用,有可能你的SQL語(yǔ)句包含了多次從一個(gè)表進(jìn)行查詢后進(jìn)行連接組合。比如你要比較每年的顧客數(shù)同先前年的顧客數(shù)的變化,所以你的查詢就必須JOIN了2個(gè)相同的表的實(shí)例,這也是不可避免的。
Common Table Expressions (CTE)
CTE是在SQL2005新加入的一種表的表示類(lèi)型。
它的定義如下:
WITH cte_name
AS
(
cte_query
)
outer_query_refferring to_cte_name;
注意:因?yàn)樵跇?biāo)準(zhǔn)的T-SQL語(yǔ)言中已經(jīng)包含了WITH關(guān)鍵字,所以為了區(qū)分,CTE在語(yǔ)句的結(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)然,作者本人有更推薦的寫(xiě)法:
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表查詢出來(lái)的結(jié)果,動(dòng)態(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í)例五(對(duì)象容器)
即提供了封裝的能力,有利于組件化的編程。作者額外的提醒,CTE無(wú)法直接內(nèi)嵌,但是可以通過(guò)把CTE封裝進(jìn)一個(gè)對(duì)象容器里并從一個(gè)外部的CTE里對(duì)這容器的數(shù)據(jù)進(jìn)行查詢而實(shí)現(xiàn)內(nèi)嵌。
作者也說(shuō)明了,使用CTEs在VIEW和UDFs里是沒(méi)有什么價(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è)例子,來(lái)講述這個(gè)在SQL2005的新內(nèi)容,CTEs的遞歸。
根據(jù)employeeId,返回此員工的信息,并包含所有下級(jí)員工的信息。(等級(jí)關(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è)索引將通過(guò)一個(gè)單獨(dú)的查詢(局部掃描)來(lái)取得每個(gè)經(jīng)理的直接下級(jí)。Include(FristName,LastName)加在這里,即是覆蓋列。
小知識(shí):什么Include索引?
Include索引是SQL2005的新功能。Include索引的列并不影響索引行的物理存儲(chǔ)順序,他們作為一個(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è)查詢?cè)贑TE的身體里類(lèi)似于一格錨點(diǎn)。這個(gè)錨點(diǎn)僅僅返回一個(gè)有效的表,并作為遞歸的一個(gè)錨。從上的例子看出來(lái),錨點(diǎn)僅僅返回了一個(gè)employeeID = 5 的一行。然后的第2個(gè)查詢是作為遞歸成員。當(dāng)查詢到下屬成員的結(jié)果為空時(shí),此遞歸結(jié)束。
如果你擔(dān)心遞歸會(huì)造成永久循環(huán),你可以使用下面的表達(dá):
WITH cte_name AS (cte_body) outer_query OPTION (MAXRECURSION n);
默認(rèn)的n為100,當(dāng)n=0時(shí),無(wú)限制。
您可能感興趣的文章:- SQLServer用t-sql命令批量刪除數(shù)據(jù)庫(kù)中指定表(游標(biāo)循環(huán)刪除)
- 如何在SQL Server 2008下輕松調(diào)試T-SQL語(yǔ)句和存儲(chǔ)過(guò)程
- SQL Server中的T-SQL的基本對(duì)象
- SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記(4)
- SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記(3)
- SqlServer 2005 T-SQL Query 學(xué)習(xí)筆記(2)
- SQLServer 2008 新增T-SQL 簡(jiǎn)寫(xiě)語(yǔ)法
- SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
- SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
- SQL Server 數(shù)據(jù)庫(kù)管理常用的SQL和T-SQL語(yǔ)句
- T-sql語(yǔ)句修改SQL Server數(shù)據(jù)庫(kù)邏輯名、數(shù)據(jù)庫(kù)名、物理名的方法