1螺捐、派生表
實(shí)質(zhì)：就是特殊的子查詢(xún)（將查詢(xún)結(jié)果放在from后面）
含有一張Order表：
看下面的sql語(yǔ)句：
select
orderid,orderdate,custid
from
(
select
orderid,orderdate,custid,ROW_NUMBER() over(order by orderid) as rownum--列明必須起別名
from [Sales.Orders]
) as t--表名必須起別名
需要注意的特殊之處：查詢(xún)出來(lái)的表要起別名颠悬，子查詢(xún)里面的字段名也要起別名
2矮燎、CTE（公共表的表達(dá)式）
1）語(yǔ)法規(guī)范：
with use_country--定義的表名
as--上面的是 語(yǔ)法：
(
select country,companyname,custid
from [Sales.Customers]
where country='按時(shí)打算'
)
select * from use_country;--將查詢(xún)語(yǔ)句 寫(xiě)在 子查詢(xún)的后面
將查詢(xún)語(yǔ)句放在最后。
下面看幾個(gè)例子赔癌，具體解析一下：
業(yè)務(wù)邏輯：還是用上面的那張Order表诞外，要求：查詢(xún)出 每年的訂單數(shù)量 大于10的 用戶(hù)
1》普通的寫(xiě)法：
select
custid,year(orderdate),COUNT(1) as '訂單數(shù)量'
from [Sales.Orders]
group by YEAR(orderdate), custid
having COUNT(orderid)>10 --having:對(duì) group by 之后的 組函數(shù) 進(jìn)行 篩選使用
2》使用派生表：
select
custid,orderyear,orderNum
from
(
select
custid,orderyear,COUNT(*) as orderNum
from
(
select
custid, year(orderdate) as orderyear
from [Sales.Orders]
) as t1
group by orderyear,custid
) as t2
where orderNum>10
上面的 查詢(xún)的層次只有兩層，如果層次非常多的話(huà)灾票，就會(huì)出現(xiàn)峡谊，看起來(lái)非常費(fèi)勁了，所以使用CTE的方式更簡(jiǎn)潔
with OrderYear
as
(
select
custid,YEAR(orderdate) as orderyear
from [Sales.Orders]
),
OrderGroupYear
as
(
select
custid,orderyear,COUNT(orderyear) as ordernum
from OrderYear
group by orderyear,custid
),
OrderNumThanTen
as
(
select
custid,orderyear,ordernum
from OrderGroupYear
where ordernum>10
)
select * from OrderNumThanTen;
2）CTE還可以多張表引用：（相當(dāng)于c#里面的將重復(fù)的代碼封裝成一個(gè)方法）下面舉例：
業(yè)務(wù)邏輯：查詢(xún)出 每年 客戶(hù)的數(shù)量刊苍，以及 前后兩年之間客戶(hù)數(shù)量的差量
--首先查詢(xún)出每年叫客戶(hù)的數(shù)量：
select
year(orderdate),COUNT(distinct custid)--將 重復(fù)的 客戶(hù) 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
然后使用派生表的方式實(shí)現(xiàn)：
select
currtYearOrder.orderYear,currtYearOrder.custCount,prevYearOrder.orderYear,prevYearOrder.custCount,currtYearOrder.custCount-prevYearOrder.custCount
from
(
select
year(orderdate) as orderYear,COUNT(distinct custid) as custCount--將 重復(fù)的 客戶(hù) 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
) as currtYearOrder
left outer join
(
select
year(orderdate) as orderYear ,COUNT(distinct custid) as custCount--將 重復(fù)的 客戶(hù) 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
) as prevYearOrder
on currtYearOrder.orderYear=prevYearOrder.orderYear+1
下面使用CTE的方式實(shí)現(xiàn)既们，就不用重復(fù)：
with OrderYearCust
as
(
select
year(orderdate) as orderYear,COUNT(distinct custid) as custCount--將 重復(fù)的 客戶(hù) 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
)
select
curtYearOrder.orderYear,curtYearOrder.custCount,prevYearOrder.orderYear,prevYearOrder.custCount,curtYearOrder.custCount-prevYearOrder.custCount
from OrderYearCust as curtYearOrder left outer join OrderYearCust as prevYearOrder on curtYearOrder.orderYear=prevYearOrder.orderYear+1
3）CTE遞歸查詢(xún)：（針對(duì) 樹(shù)形節(jié)點(diǎn) 進(jìn)行查詢(xún)）
表的結(jié)果如下：
業(yè)務(wù)邏輯：需要查詢(xún)出 mgrid 為 2 下面的 所有的子節(jié)點(diǎn)
with diguiEmployee
as
(
--起點(diǎn):最上層的 查詢(xún)(只執(zhí)行一次)
select
enpid,lastname,firstname,mgrid
from [HR.Employees]
where mgrid=6
union all --連接 起點(diǎn):上層查詢(xún) 和遞歸查詢(xún)
--遞歸查詢(xún)
select
e.enpid,e.lastname,e.firstname,e.mgrid
from [HR.Employees] as e inner join diguiEmployee as d
on e.mgrid=d.enpid
)
select enpid,lastname,firstname,mgrid from [HR.Employees] where enpid=6
union all --將查詢(xún)的 結(jié)果 連接 起來(lái)
select * from diguiEmployee;
