SQL Server 2005 技术内幕 TSQL查询学习笔记chapter4之二表表达式Table Expressions
用到的数据库NorthWind
一:派生表
定义:我们把派生表当成普通表来处理,所有它出现在From语句后.派生表完全是虚拟的,没有被具体化.所有我们使用它性能没有降低和提高,作用仅仅是为了代码更清晰和简化.
格式:
From(derived_table_query expression) AS derived_table_alias
1:在派生表中创建别名使其在外部查询中可见.在查询中select列表中使用别名没有多大意思.因为select只早于order by 比如下面的语句将失败
select year(OrderDate) As OrderYear From Orders
Group by OrderYear--别名OrderYear这个时候还访问不到
正确写法是:
select OrderYear From(select year(Orderdate) AS OrderYear From Orders) AS D <SQL语句1>
Group by OrderYear
当然可以不使用派生表,举例只是说明派生表的特性.
2:可以在同批次中引用定义的参数.派生表可以嵌套(不喜欢嵌套,不直观,难已理解,用CTE更好点)
3:多引用.不能在同一查询中多次引用同一个派生表.必须创建多个
二:公用表表达式(CTE)(2005版本后新的内容)
格式:
其他语句;--用分号结束语句是一个好的习惯
With cte_name AS
(cte_query)
1:<SQL语句1>的cte版本
with c AS
(select year(Orderdate) AS OrderYear From Orders)
select OrderYear From c group by OrderYear
2:cte不能别嵌套,with语句可以定义多个cte
Ex1:Orders表中查询消费者数量超过70的年份和这一年消费者总量
思路: 对年份进行分组,计算这一年不同消费者数量
with c1 AS
(select year(OrderDate) AS OrderYear,CustomerId From Orders),
--不同cte间用逗号相隔
c2 AS
(select OrderYear,count(distinct CustomerId) AS NumsCustomer From c1 group by OrderYear)
select Orderyear,NumsCustomer From c2 where NumsCustomer>70
当然可以用下面的语句,不使用cte,这里只是举例说明cte的特性
select year(OrderDate) AS OrderYear,count(distinct CustomerId) AS NumsCustomer From Orders group by year(OrderDate) having count(distinct CustomerId)>70
3:可以在查询中多次调用同一个cte
Ex2:Orders表中比较每年和上年的消费者数量
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;
