sql之表的表达式

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;