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(Orde
相关新闻>>
- 发表评论
-
- 最新评论 更多>>