1、派生表
实质：就是特殊的子查询（将查询结果放在from后面）
含有一张Order表：
看下面的sql语句：
select
orderid,orderdate,custid
from
(
select
orderid,orderdate,custid,ROW_NUMBER() over(order by orderid) as rownum--列明必须起别名
from [Sales.Orders]
) as t--表名必须起别名
需要注意的特殊之处：查询出来的表要起别名，子查询里面的字段名也要起别名
2、CTE（公共表的表达式）
1）语法规范：
with use_country--定义的表名
as--上面的是 语法：
(
select country,companyname,custid
from [Sales.Customers]
where country='按时打算'
)
select * from use_country;--将查询语句 写在 子查询的后面
将查询语句放在最后。
下面看几个例子，具体解析一下：
业务逻辑：还是用上面的那张Order表，要求：查询出 每年的订单数量 大于10的 用户
1》普通的写法：
select
custid,year(orderdate),COUNT(1) as '订单数量'
from [Sales.Orders]
group by YEAR(orderdate), custid
having COUNT(orderid)>10--having:对 group by 之后的 组函数 进行 筛选使用
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
上面的 查询的层次只有两层，如果层次非常多的话，就会出现，看起来非常费劲了，所以使用CTE的方式更简洁
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还可以多张表引用：（相当于c#里面的将重复的代码封装成一个方法）下面举例：
业务逻辑：查询出 每年 客户的数量，以及 前后两年之间客户数量的差量
--首先查询出每年叫客户的数量：
select
year(orderdate),COUNT(distinct custid)--将 重复的 客户 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
--然后使用派生表的方式实现：
select
currtYearOrder.orderYear,currtYearOrder.custCount,prevYearOrder.orderYear,prevYearOrder.custCount,currtYearOrder.custCount-prevYearOrder.custCount
from
(
select
year(orderdate) as orderYear,COUNT(distinct custid) as custCount--将 重复的 客户 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
) as currtYearOrder
left outer join
(
select
year(orderdate) as orderYear ,COUNT(distinct custid) as custCount--将 重复的 客户 要去掉
from [Sales.Orders]
group by YEAR(orderdate)
) as prevYearOrder
on currtYearOrder.orderYear=prevYearOrder.orderYear+1
--下面使用CTE的方式实现，就不用重复：
with OrderYearCust
as
(
select
year(orderdate) as orderYear,COUNT(distinct custid) as custCount--将 重复的 客户 要去掉
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递归查询：（针对 树形节点 进行查询）
表的结果如下：
业务逻辑：需要查询出 mgrid 为 2 下面的 所有的子节点
with diguiEmployee
as
(
--起点:最上层的 查询(只执行一次)
select
enpid,lastname,firstname,mgrid
from [HR.Employees]
where mgrid=6 union all --连接 起点:上层查询 和递归查询 --递归查询
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 --将查询的 结果 连接 起来
select * from diguiEmployee;