SQL Server中With As的介绍与应用（三）--递归的实战应用

Vaccae

发布于 2019-07-24 14:03:44

1.3K0

发布于 2019-07-24 14:03:44

文章被收录于专栏：微卡智享

前言

前一篇《SQL Server中With As的介绍与应用（二）--递归的使用》我们介绍了一下SQL中With As的递归应用，本章我们直接通过递归的方式实战操作一下，看看使用的效果。

报表要求

我们要查2019-05-20到2019-05-31的销售数据，列出每天的销售额是多少，大概的要求如下图

销售表的数据源

按日期分组看一下

从结果上看只有2019-05-22的销售数据，如果要实现上面的报表样子相用这个select查询是少了很多天的数据。

今天我们就用普通实现和用With As实现两种实现方式来看看怎么实现这个报表查询。

普通实现方式

普通实现试的思路：

先创建个临时表
把选择的日期数据先插入进去
然后跟据数据源的数据更新临时表数据
查询临时表数据生成报表

代码实现

--定义变量开始日期和结束日期还有临时销售表
declare @sdate datetime,@edate datetime
declare @tbxs table(销售日期 datetime,销售额 decimal(19,4))
--赋值开始和结束日期
select @sdate='2019-05-20'
select @edate='2019-05-31'

--把所有日期数据先插入销售额为0
while(@sdate<=@edate)
begin
  insert into @tbxs(销售日期,销售额)
  values(@sdate,0)
  select @sdate=dateadd(day, 1, @sdate)
end

--更新临时表中数据的信息
update V set 销售额=b.销售额 from @tbxs V,
(select 销售日期,SUM(销售金额) as 销售额 from 商品流水表
group by 销售日期) b where V.销售日期=b.销售日期

--显示最后的报表信息
select * from @tbxs

实现效果

上面可以看出来我们用普通的实现方式，需要定义一个临时表，并且对临时表先插入后修改的操作，最后再查询用于实现。

接下来再看看With As的实现

With As实现

With As实现思路：

利用With As实现开始日期到结束日期的递归利用With As实现把销售数据分组
通过上面两个组合的数据用左连接直接查询

我们上一篇中说过，With As可以设置多个，中间用（,）逗号分隔即可，所以我们上面的两个算到一步去了。

代码实现

--定义变量开始日期和结束日期还有临时销售表
declare @sdate datetime,@edate datetime
--赋值开始和结束日期
select @sdate='2019-05-20'
select @edate='2019-05-31'

--用With As把开始日期和结束日期进行递归生成公共名为“日期”的表
--把销售数据分组查询出来生成公共名为"销售"的表
;with 日期 as(
   select 销售日期=cast(@sdate as datetime)
   union all
   select 销售日期 = dateadd(day, 1, 日期.销售日期) from 日期
   where 日期.销售日期<cast(@edate as datetime)
   ),
   销售 as (
   select 销售日期,SUM(销售金额) as 销售额 from 商品流水表
   group by 销售日期
   )
   
--通过”日期“的表左联连”销售“的表直接查询出我们要的数据
select a.销售日期,isnull(b.销售额,0) as 销售额 
from 日期 a Left Join 销售 b ON a.销售日期=b.销售日期

实现效果