报表设计中的几个SQL技巧

使用专业的报表工具设计报表模板时，数据准备是一个关键环节。数据准备的核心，实际上是数据库查询SQL语句的编写。

在一个典型的交叉分析报表中，需要显示每个大区、每个月度的销售业绩，如下图：

这个报表的数据集查询语句大致如下：

select DATEPART(month,订购日期) 月份
, 销售大区
, SUM(订单金额) 销售额
from Demo_销售明细
where 订购日期 >='2019-1-1'
 and  订购日期 <'2020-1-1'
 group by DATEPART(month,订购日期)
 , 销售大区
 

年初时，由于数据很少，表格列上的月份只有1个月，或者很少几个月份，表格行上的大区可能也很少，表格可能会很难看，如下图：

如果想要保证表格的样式不受数据量的影响，就要自动“补齐”月份和大区的列和行。为此，需要在准备报表数据集时想办法。

首先，准备月份列表，12个月，每个月一条记录，SQL语句如下：

select 1 M
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

这个语句是一个不带FROM子句的SQL，其中第一个语句 select 1 M 中的M，是字段别名。后续语句通过UNION合并起来，结果就是一个单字段、12条记录的数据集。如下图：

然后，准备大区列表。为了避免因某些大区暂无销售业绩导致大区列表不全，应从基础资料表查询全部的大区。如果没有这种基础资料表，也可以直接从所有年度的销售数据中抽取大区列表，语句的核心是DISTINCT关键字，SQL语句如下：

select distinct 销售大区 from Demo_销售明细
 

有了这两个列表，先将这两个列表通过无条件JOIN生成两者的交叉组合全集，再通过LEFT JOIN与实际销售数据关联，即可保证没有业绩的大区-月份组合也会出现在报表数据集中。SQL语句如下：

select MR.M, MR.销售大区, Sales.销售额 from
( select * from 
(  select 1 M
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
) Months
 inner join
(  select distinct 销售大区 from Demo_销售明细 ) Regions
on 1=1
) MR left join
(
select DATEPART(month,订购日期) 月份
, 销售大区
, SUM(订单金额) 销售额
from Demo_销售明细
where 订购日期 >='2017-1-1'
 and  订购日期 <'2017-1-2'
 group by DATEPART(month,订购日期)
 , 销售大区
) Sales
 on Sales.月份=MR.M and Sales.销售大区=MR.销售大区
 

上面这种SQL语句可能看起来很复杂，不易理解。可以采用“通用表表达式（CTE）”改写为：

with Months as
(  select 1 M
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
)
, Regions as
(  select distinct 销售大区 from Demo_销售明细
)
, Sales as
( select DATEPART(month,订购日期) 月份
, 销售大区
, SUM(订单金额) 销售额
from Demo_销售明细
where 订购日期 >='2017-1-1'
 and  订购日期 <'2017-1-2'
 group by DATEPART(month,订购日期)
 , 销售大区
)

select M.M, R.销售大区, Sales.销售额 from
 Months M inner join Regions R on 1=1
 left join Sales  on Sales.月份=M.M and Sales.销售大区=R.销售大区

这样的SQL语句相当于创建了3个临时的视图：Months，Regions，Sales，然后基于这些视图做查询。最终生成的数据集记录如下图：

绑定到报表表格之后，显示效果如下图：

可以看到，尽管大多数月份和大区都没有业绩数据，报表仍然完整显示了表格框架。

 

小结：

（1）用 select 1 union select 2... 这种UNION起来的常数字段值可生成临时的有限数据集合，比如月份列表。

（2）使用DISTINCT关键字，可从业务数据中获取某个分类的完整列表。

（3）使用CTE可简化复杂SQL的结构，既容易看懂，也便于调试。