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

收藏

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

原始的数据如下图：

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

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

有一个办法是在数据库创建一个月份表，只有一个【月份】字段，预置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.月份