上一篇《如何在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 @salevalues('张三',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 mycteas(select * from @sale)select * from mycte;
从脚本中我们可以看出,公用表表达式的定义是不可以使用分号“;”的,她是依附于其后紧接着的那条语句,这个语句可以是select、insert、update或者delete都行。
如何批量定义多个公用表表达式?
公用表表达式支持批量定义是一大特色,这样的好处是显而易见的,因为后面的表达式可以引用前面的表达式,而调用的语句可以引用所有的表达式,这对我们对数据进行各种整理非常有益。
比如我们要基于第一个表达式将下个月度的销售额预测提高50%,脚本如下:
with mycte1as(select * from @sale),mycte2as(--下个月业务预计提升50%select2019 as FYear,1 as FMonth,FName,FDistrict,cast(FAmount*1.5 as decimal(28,10)) as FAmountfrom mycte1)select * from mycte1union allselect * from mycte2;
如何定义递归调用的公用表表达式?
所谓递归调用,是公用表达式里面的查询直接调用表达式自身,通过Union All把多个查询连接,第一个查询是基础查询,后面可以Union All多个查询直接对表达式进行调用。
我们这里用递归调用来实现对2018年的销售额上浮50%,脚本如下:
with mycteas(select *,1 as FLevelfrom @saleunion allselect2019 as FYear,1 as FMonth,FName,FDistrict,FAmount*1.5 as FAmount,2 as FLevelfrom mycte where FLevel=1)select * from mycte;
为了避免递归调用发生死循环的情况,您可以增加一个表示层级的字段,比如FLevel,在后续查询递归调用时加上层级限定条件。安全起见,也可以使用option(MAXRECURSION 递归级别)子句加以限制。
如何在视图中使用公用表表达式?
可以在视图中嵌入公用表表达式是一大特色,我们之前写的视图,大多是干巴巴的查询语句,想要做更复杂的处理就很容易混乱,嵌入公用表表达式,可以使我们的视图更清晰、功能更强大。参看下面的例子:
create view dbo.myviewaswith mycte(FName,FAssis,FBarcode)as(select FName,FAssis,FBarcode from idata)select * from mycte;
从脚本可以看出,公用表表达式直接嵌入在视图定义的as后面、select之前的位置。当然,您可以定义多个或递归表达式,这样写出来的视图就更强大。
希望对您有所帮助!