在开发统计报表的过程中，经常会碰到在查询到的数据集中，插入一些小计行和合计行。比如在烤烟系统中，几乎每个统计报表都需要加入“合计”行，还有不少涉及到烟叶等级的统计报表需要加入各烟叶等级的小计行。我看到不少人（包括我自己）都是在程序中专门写一些方法来处理的，有的方法还很繁琐。最近在看SQL Server2000的联机丛书中才发现，利用GROUPING聚合函数和ROLLUP运算符可以轻松实现统计中加入小计和合计功能。
[@more@]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
查询的结果略去
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 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/8786886/viewspace-918732/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
谈Oracle的回闪查询
请登录后发表评论
登录
全部评论
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 违法和不良信息举报电话:18101376593 |
