月季年度不同期间的业务数据查询结果合并

业务系统中经常需要统计不同期间的业务数据，以销售收入统计为例，业务主管可能需要在一张表格中同时显示截止到某一个日期（往往就是当前日期）的当日合计、月度合计、季度合计、年度累计，如下表：

统计日期	产品	当日收入	月度收入	季度收入	年度收入
2019-5-25	产品001	100	2000	30000	400000
2019-5-25	产品002	200	2100	28000	350000

如果使用编程语言，统计不同时间段的合计，在逻辑上很简单，只是工作量和程序性能问题。如果想在一个查询语句中同时输出这些不同时间段的合计数，就没那么简单了。

下面以一个虚拟的【业务收入】表为例，介绍这个统计查询的实现思路。该表的结构如下图：

因为是带条件的查询，所以首先需要定义一个【统计日期】的变量，并赋值，比如：

declare @d date
set @d='2019-5-25' -- 或者 set @d=getdate() 设置当期系统日期为统计截止日期

然后，分别查询当日合计、月度合计、季度合计、年度合计。

当日合计的SQL如下：

select  日期,产品
,sum(收入) 当日收入
 from 业务收入
where 1=1
and 日期 = @d
group by  日期,产品

月度合计的SQL很类似，只是在where条件中有区别：

select  @d 日期,产品 -- 此处将 @d 输出为【日期】字段，是为了显示统计日期
,sum(收入) 月度收入
from 业务收入
where 1=1
and 日期 <= @d
and 日期 >= CONVERT(datetime,CONVERT(char(8),@d, 120)+'1') -- 这是取月初1号的日期
group by  产品

季度合计与此类似，只是起始日期条件有变化：

select  @d 日期,产品
,sum(收入) 季度收入
from 业务收入
where 1=1
and 日期 <= @d
and 日期 >= DATEADD(quarter, datediff(quarter,0, @d),0) -- 这是取季度首日的日期
group by  产品
 

同样的，年度合计是从年初1月1日开始，至统计截止日期：

select  @d 日期,产品
,sum(收入) 年度收入
from 业务收入
where 1=1
and 日期 <= @d
and 日期 >= CONVERT(datetime,CONVERT(char(4),@d, 120)+'-1-1') -- 这是取年初1月1日的日期
group by  产品

直接运行上面的这些SQL语句，得到的查询结果将是4个分离的结果集，如下图：

为了合并这4个结果集，首先将每个数据集都添加几个null字段，然后使用UNION ALL将它们合并起来。

比如，对于当日收入，添加3个null字段，分别是月度收入、季度收入、年度收入，SQL语句变成：

select  日期,产品
,sum(收入) 当日收入
,null 月度收入 -- 新增null字段
,null 季度收入 -- 新增null字段
,null 年度收入 -- 新增null字段
 from 业务收入
where 1=1
and 日期 = @d
group by  日期,产品

其他3个SQL语句采用类似方法，使得4个SQL语句都输出4个统计周期的合计收入，如下图：

使用UNION ALL将4个SQL语句合并，然后再按日期、产品进行汇总，最终的SQL语句如下：

declare @d date
set @d='2019-5-25'

select 日期,产品
,sum(当日收入) 当日收入
,sum(月度收入) 月度收入
,sum(季度收入) 季度收入
,sum(年度收入) 年度收入
from
(
select
 日期,产品
,sum(收入) 当日收入
,null 月度收入
,null 季度收入
,null 年度收入
 from 业务收入
where 1=1
and 日期 = @d
group by  日期,产品

union all

select
 @d 日期,产品
,null 当日收入
,sum(收入) 月度收入
,null 季度收入
,null 年度收入
from 业务收入
where 1=1
and 日期 <= @d
and 日期 >= CONVERT(datetime,CONVERT(char(8),@d, 120)+'1')
group by  产品

union all

select
 @d 日期,产品
,null 当日收入
,null 月度收入
,sum(收入) 季度收入
,null 年度收入
from 业务收入
where 1=1
and 日期 <= @d
and 日期 >= DATEADD(quarter, datediff(quarter,0, @d),0)
group by  产品

union all

select
 @d 日期,产品
,null 当日收入
,null 月度收入
,null 季度收入
,sum(收入) 年度收入
from 业务收入
where 1=1
and 日期 <= @d
and 日期 >= CONVERT(datetime,CONVERT(char(4),@d, 120)+'-1-1')
group by  产品

) V
group by
  日期,产品
 

执行这个SQL语句，最终结果如下：

这个结果，正是期待的结果集形式。