上一篇《如何在SQL中正确使用公用表表达式》中我们详细讲解了SQL Server中的公用表表达式的概念和语法，本篇我们将结合实例，加深对公用表表达式的理解并实战一番。

我们常用的数据库主要有MySQL、PostgreSQL、SQL Server等，这些数据库系统对表类的支持各具特点，但对公用表表达式的支持算是SQLServer的一大特色。

SQLServer除了支持本地表之外，还支持临时表、表变量、公用表表达式、表类型、优化内存表等，其中临时表和表变量非常容易混淆。有需要了解的朋友通过链接可进入直接调阅。

演示数据准备

为了演示方便，我们先准备一些演示数据，就以我常用的销售表为例：

declare @sale table(
FName nvarchar(50),
FYear smallint,
FMonth smallint,
FDistrict nvarchar(50),
FAmount decimal(28,10)
);
insert into @sale
values
('张三',2018,12,'北京',20000),
('张三',2018,12,'上海',50000),
('张三',2018,12,'深圳',40000),
('张三',2018,12,'广州',30000),
('李四',2018,12,'北京',30000),
('李四',2018,12,'上海',50000),
('李四',2018,12,'深圳',40000),
('李四',2018,12,'广州',10000);

这里定义了一个销售表表变量，记录2018年12月份的销售额。

如何定义公用表表达式？

下面我们定义一个简单的公用表表达式：

with mycte
as
(
select * from @sale
)
select * from mycte;

从脚本中我们可以看出，公用表表达式的定义是不可以使用分号“;”的，她是依附于其后紧接着的那条语句，这个语句可以是select、insert、update或者delete都行。

如何批量定义多个公用表表达式？

公用表表达式支持批量定义是一大特色，这样的好处是显而易见的，因为后面的表达式可以引用前面的表达式，而调用的语句可以引用所有的表达式，这对我们对数据进行各种整理非常有益。

比如我们要基于第一个表达式将下个月度的销售额预测提高50%，脚本如下：

with mycte1
as
(
select * from @sale
),
mycte2
as
(--下个月业务预计提升50%
select
2019 as FYear,
1 as FMonth,
FName,FDistrict,
cast(FAmount*1.5 as decimal(28,10)) as FAmount
from mycte1
)
select * from mycte1
union all
select * from mycte2;

如何定义递归调用的公用表表达式？

所谓递归调用，是公用表达式里面的查询直接调用表达式自身，通过Union All把多个查询连接，第一个查询是基础查询，后面可以Union All多个查询直接对表达式进行调用。

我们这里用递归调用来实现对2018年的销售额上浮50%，脚本如下：

with mycte
as
(
select *,1 as FLevel
from @sale
union all
select
2019 as FYear,
1 as FMonth,
FName,
FDistrict,
FAmount*1.5 as FAmount,
2 as FLevel
from mycte where FLevel=1
)
select * from mycte;

为了避免递归调用发生死循环的情况，您可以增加一个表示层级的字段，比如FLevel，在后续查询递归调用时加上层级限定条件。安全起见，也可以使用option(MAXRECURSION 递归级别)子句加以限制。

如何在视图中使用公用表表达式？

可以在视图中嵌入公用表表达式是一大特色，我们之前写的视图，大多是干巴巴的查询语句，想要做更复杂的处理就很容易混乱，嵌入公用表表达式，可以使我们的视图更清晰、功能更强大。参看下面的例子：

create view dbo.myview
as
with mycte(FName,FAssis,FBarcode)
as
(
select FName,FAssis,FBarcode from idata
)
select * from mycte;

从脚本可以看出，公用表表达式直接嵌入在视图定义的as后面、select之前的位置。当然，您可以定义多个或递归表达式，这样写出来的视图就更强大。

希望对您有所帮助！