SQL Server CTE (Common Table Expression) 公用表表达式

参考: 

Sql — CTE公用表表达式和With用法总结

YouTube – SQL WITH Clause | How to write SQL Queries using WITH Clause | SQL CTE (Common Table Expression)

 

特色

1. CTE 可以引用自身, 实现递归查询. (Oracle 用 connect by prior)

2. 它有点像表变量, 其后的 query 都可以引用它. 然后自动销毁. 很方便

3. 可读性很棒

 

复用与可读性优化

来看一个例子, 有一个 product 表

go
create table product (
  id int identity primary key,
  name nvarchar(max) not null default '',
  [group] nvarchar(max) not null default '',
  price decimal not null default 0
);

insert into product (name, [group], price) values 
('product 1', 'group 1', 100),
('product 2', 'group 1', 100),
('product 3', 'group 1', 100),
('product 4', 'group 2', 100),
('product 5', 'group 2', 150),
('product 6', 'group 2', 100),
('product 7', 'group 3', 100),
('product 8', 'group 3', 50),
('product 9', 'group 3', 100);
go

需求是

1. 把 product group by [group], 并且计算出 sum price as total_price

2. 计算出 ave total_price 

3. filter 出 total_price >= ave_total_price 的 group

Without CTE

首先是 group by [group] 语句

select [group], sum(price) as total_price from product group by [group];

然后是 ave 语句

select cast(avg(total.total_price) as int) as avg_price from (
    select [group], sum(price) as total_price from product group by [group]
) total;

注意, 中间的语句是重复的 (管理扣分)

最后是

select [group], total_price from 
(select [group], sum(price) as total_price from product group by [group]) pg 
inner join 
(select cast(avg(total.total_price) as int) as avg_price from 
    (
        select [group], sum(price) as total_price from product group by [group]
    ) total
) pap 
on pg.total_price >= pap.avg_price;

虽然结果是正确的, 但是语句重复很多, 可读性太差了.

With CTE

with 
product_group as 
(
   select [group], sum(price) as total_price from product group by [group]
), 
product_avg_price as 
(
    select cast(avg(total_price) as int) as avg_price from product_group
)
select [group], total_price from product_group pg inner join product_avg_price pap on 
pg.total_price >= pap.avg_price;

没有了重复, 有了结构, 可读性大大提升.

 

递归 parent child tree

CTE 还有一个常用的地方是递归找出所有子层.

go
create table category (
  id int identity primary key,
  name nvarchar(256) not null default '',
  parentId int null
);
alter table category add constraint fk_category_category_parentId foreign key (parentId) references category (id) on delete no action;

insert into category (name, parentId) values 
('category root', null),
('category layer 1 a', 1),
('category layer 1 b', 1),
('category later 2 aa', 2),
('category later 2 ba', 3),
('category later 3 aaa', 4),
('category later 3 aab', 4);
go

需求是找出 category layer 1 a 旗下的所有 category

with loop_category as (
  select id, name, parentId from category where name = 'category layer 1 a'
  union all 
  select c.id, c.name, c.parentId from category c inner join loop_category lc on c.parentId = lc.id
)
select * from loop_category;

关键就是 with as 里面可以 unial all loop_category 实现递归.