巧用SQL语句补足不完全数据报表

决策支持报表系统中经常需要用到一类按照月份对比分析某个维度业务数据的报表，下图是一个销售分析的例子：

原始的数据如下图：

这个数据集有个问题：不是每个区域、每个月都有销售数值，这样，在使用ActiveReports报表的矩表元素时，默认的展示效果是这样的：

 

显然，表格栏目缺少一些月份列，既不美观，也不便于对比分析各月份的销售业绩。如何“补足”缺失的部分月份数据呢？

有一个办法是在数据库创建一个月份表，只有一个【月份】字段，预置12条记录，字段值分别是1，2，…，12。然后用这个月份表与销售数据表做LeftJoin。

遗憾的是，设计报表的人员往往只有数据库的只读权限，增删改数据都不行，更不要提创建一个仅供报表使用的表对象了。

幸好，包括SQLServer在内的很多数据库管理系统都支持公用表表达式（Common Table Expression，简称CTE），将12个月份放进一个CTE，再将【区域】放进另一个CTE，然后采用子查询的方式获得每个【月份-区域】组合的销售额，即可保证每个月、每个区域都有数据，原始数据表中没有数据的月份或区域，销售额将是0。

完成后的SQL语句如下

with Month_CTE(月份) as
(
select 1 union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10 union select 11 union select 12
)
,Region_CTE(区域) as
(
select distinct 区域 from T2
)
select
M.月份
,R.区域
,[销售额]=isnull((select SUM(销售额) from T2 where T2.月份=M.月份 and T2.区域=R.区域),0)
from Month_CTE M, Region_CTE R
order by 1,2

查询结果如下图所示：

在这个查询结果中，每个月份与区域的组合，都有一条销售数值的记录。在此基础上，简单地设计一个下图所示的矩表：

就能得到下面这样的销售分析表：

可以看到，这个效果正是本文开头的最初需求。