用SQL实现统计报表中的“小计”和“合计”

在开发统计报表的过程中，经常会碰到在查询到的数据集中，插入一些小计行和合计行。比如在烤烟系统中，几乎每个统计报表都需要加入“合计”行，还有不少涉及到烟叶等级的统计报表需要加入各烟叶等级的小计行。我看到不少人（包括我自己）都是在程序中专门写一些方法来处理的，有的方法还很繁琐。最近在看SQL Server2000的联机丛书中才发现，利用GROUPING聚合函数和ROLLUP运算符可以轻松实现统计中加入小计和合计功能。

1.      GROUPPING和ROLLUP的基本知识

1.1.    GROUPPING

是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。

语法: GROUPING ( column_name )

参数: column_name是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。

返回类型: int

备注: 分组用于区分由 CUBE 和 ROLLUP 返回的空值和标准的空值。作为CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。

1.2.    ROLLUP

ROLLUP 运算符生成聚合汇总, 需要汇总信息时，此运算很有用。该运算符生成的结果集类似于 CUBE 运算符生成的结果集。但它们两者有一些区别，CUBE 生成的结果集显示了所选列中值的所有组合的聚合。而ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

语法: ROLLUP ( column_name1[，column_name2。。。] )

用法: 用在GROUP BY 子句中。对那些需要按其分组，并对其分组的聚合数据进行汇总的列，就请对这些列加上ROLLUP运算符。

注意: “GROUP BY ROLLUP(col1,col2)”与“GROUP BY ROLLUP(col1),ROLLUP(col2)”是有区别的。“GROUP BY ROLLUP(col1),ROLLUP(col2)”其实就相当于“GROUP BY CUBE(col1,col2)”，因为它对每个分组的聚合都要进行汇总。“GROUP BY ROLLUP(col1,col2)”与“GROUP BY ROLLUP(col2,col1)”也有区别，前者是对每个col1的唯一值都产生一个在col1下各个col2聚合值汇总的行，再对所有col1与col2的聚合值产生一个汇总行；而后者是对每个col2的唯一值都产生一个在col2下各个col1聚合值的汇总行，再对所有col1与col2的聚合值产生一个汇总行。这样说逻辑可能不太清晰，我们看一下下面的图表就一目了然了。

col1	col2	amount	col1	col2	amount
3	2	3584777	3	2	3584777
3	3	12774875	4	2	200789.1
3	16359652	5	2	274432
4	2	200789.1	2	4059998
4	3	8619498	3	3	12774875
4		8820288	4	3	8619498
5	2	274432	5	3	2024463
5	3	2024463	3	23418837
5	2298895	27478835
27478835
GROUP BY ROLLUP(col1,col2)的效果	GROUP BY ROLLUP(col2,col1)的效果

 

2.      实际案例

我们现在就来看一个Oracle9i中的统计示例，示例如下：

2.1.    统计要求

开发一个关于各烟叶等级的二次验级的原发件数、原发重量及验收重量的统计报表。其中，原发件数、原发重量和验收重量等列要求计算出各等级组别的小计和所有记录的合计。

2.2.    我们通常的做法

1.用下面的SQL统计出初步的数据集。

 

SELECT  T1.TOBACCO_CLASS_NAME,

                 T4.TOBACCO_CLASS_TYPE,

                NVL(SUM(T1.ORG_PIECE), 0) TOTAL_ORG_PIECE,

                 NVL(SUM(T1.ORG_WEIGHT), 0) TOTAL_ORG_WEIGHT,

                 NVL(SUM(T1.AMOUNT), 0) TOTAL_AMOUNT

   FROM  VI_FK_BALANCE_DETAIL T1, TB_TOBACCO_CLASS T4

WHERE  T1.TOBACCO_CLASS_ID=T4.TOBACCO_CLASS_ID

                 AND T1.ACCOUNT_YEAR=T4.ACCOUNT_YEAR

                 AND T4.DEL_FLAG=0

                 AND T4.ENABLE_FLAG=0

                 AND T1.REC_DATE > TO_DATE(‘2006-11-05’, ‘YYYY-MM-DD’

GROUP  BY  T4.TOBACCO_CLASS_TYPE,T1.TOBACCO_CLASS_NAME

 ORDER   BY  T4.TOBACCO_CLASS_TYPE

查询的结果如下表所示

烟叶等级	等级组别	原发件数	原发重量	验收重量
(B1F)上桔一	51	4945	197800	197508.1
(B2F)上桔二	51	8335	333400	332316.9
(C1F)中桔一	51	694	27760	27610.54
(C2F)中桔二	51	803	32120	31650.4
(C3F)中桔三	51	6381	255240	255372.6
(X1F)下桔一	51	75	3000	3012.2
(B3F)上桔三	52	4701	188040	187389.9
(B4F)上桔四	52	122	4880	4866.81
(C3V)中微青三	52	174	6960	6934.06
(C4F)中桔四	52	4639	185560	185276.4
(X2F)下桔二	52	1739	69560	69029.79
(X2V)下微青二	52	26	1040	1038.34
(X3F)下桔三	52	1263	50520	50439.86
(X4F)下桔四	53	102	4080	4075.62
(B3K)上杂三	54	0	0	249.39

                            

2.再在程序中编写相应的方法对查询得到的数据集进行处理。我们的重点不是在怎么写处理数据集的方法上，所以相应的方法在此略去。

 

 

2.3.      用SQL直接实现的方法

 

SELECT DECODE(GROUPING(T4.TOBACCO_CLASS_TYPE) + GROUPING(T1.TOBACCO_CLASS_NAME),

                               1,

                               DECODE(T4.TOBACCO_TYPE,

                                                  51, ‘上等烟小计’,

                                                  52, ‘中等烟小计’,

                                                  53, ‘下等烟小计’,

                                                  54, ‘低等烟小计’,

                                                  ‘小计’),

                               2,

                               ‘合计’,

                               T1.TOBACCO_CLASS_NAME

                 ) TOBACCO_CLASS_NAME,

                   T4.TOBACCO_CLASS_TYPE,

                   NVL(SUM(T1.ORG_PIECE),0) TOTAL_ORG_PIECE,

                   NVL(SUM(T1.ORG_WEIGHT), 0) TOTAL_ORG_WEIGHT,

                   NVL(SUM(T1.AMOUNT), 0) TOTAL_AMOUNT

 FROM     VI_FK_BALANCE_DETAIL T1, TB_TOBACCO_CLASS T4

WHERE    T1.TOBACCO_CLASS_ID=T4.TOBACCO_CLASS_ID

                   AND T1.ACCOUNT_YEAR=T4.ACCOUNT_YEAR

                   AND T4.DEL_FLAG=0

                   AND T4.ENABLE_FLAG=0

                   AND T1.REC_DATE > TO_DATE(‘2006-11-05’, ‘YYYY-MM-DD’

GROUP   BY   ROLLUP( T4.TOBACCO_CLASS_TYPE,T1.TOBACCO_CLASS_NAME)

ORDER   BY  T4.TOBACCO_CLASS_TYPE

  

通过查询得到统计结果如下表所示，该表的统计结果已经满足了统计要求。

烟叶等级	等级组别	原发件数	原发重量	验收重量
(B1F)上桔一	51	4945	197800	197508.1
(B2F)上桔二	51	8335	333400	332316.9
(C1F)中桔一	51	694	27760	27610.54
(C2F)中桔二	51	803	32120	31650.4
(C3F)中桔三	51	6381	255240	255372.6
(X1F)下桔一	51	75	3000	3012.2
上等烟小计	51	21233	849320	847470.8
(B3F)上桔三	52	4701	188040	187389.9
(B4F)上桔四	52	122	4880	4866.81
(C3V)中微青三	52	174	6960	6934.06
(C4F)中桔四	52	4639	185560	185276.4
(X2F)下桔二	52	1739	69560	69029.79
(X2V)下微青二	52	26	1040	1038.34
(X3F)下桔三	52	1263	50520	50439.86
中等烟小计	52	12664	506560	504975.1
(X4F)下桔四	53	102	4080	4075.62
下等烟小计	53	102	4080	4075.62
(B3K)上杂三	54	0	0	249.39
低等烟小计	54	0	0	249.39
合计	33999	1359960	1356771

 

 

    通过比较，相信大家也感觉到后者的独特魅力了吧。至少我在写那些对数据集处理得到小计行的方法的时候，感到虽不是很复杂，但也是有些繁琐的，远不如直接在SQL中实现小计来得潇洒、简洁。