用SQL实现统计报表中的“小计”和“合计”
来源:互联网 发布:用友协同软件 编辑:程序博客网 时间:2024/06/04 06:05
用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统计出初步的数据集。
查询的结果如下表所示
烟叶等级
等级组别
原发件数
原发重量
验收重量
(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直接实现的方法
通过查询得到统计结果如下表所示,该表的统计结果已经满足了统计要求。
烟叶等级
等级组别
原发件数
原发重量
验收重量
(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中实现小计来得潇洒、简洁。
- 用SQL实现统计报表中的“小计”和“合计”
- 用SQL实现统计报表中的“小计”和“合计”
- 用SQL实现统计报表中的“小计”和“合计”
- 用SQL实现统计报表中的(小计)和(合计)
- 水晶报表中按页统计字段(即每页小计)最后合计,怎么实现的?
- 报表的小计、合计
- sql 小计合计排序
- sql 小计 合计 排序
- 报表演变历史-分组报表-小计合计
- SQL小计合计并排序
- Sql分组合计小计查询
- sql 查询数据小计 合计
- 运用Reporting Service中合计,小计,Pivot等功能快速实现报表
- 报表演变历史-多层分组报表-小计合计
- 报表演变历史-分组报表-小计合计-其他2
- 报表演变历史-分组报表-小计合计-其他
- sql Asp.net 生成 小计 合计
- 小计 合计
- 正则表达式介绍及使用
- sscanf
- onmousemove 与onmouseout的测试效果
- 使用google chart在IE里的问题(在FIREFOX, CHROME没有问题)
- Oracle中几个关于日期方面的SQL实例
- 用SQL实现统计报表中的“小计”和“合计”
- 2011.8.19(android中launcher启动过程中对widget做的一些操作)
- 37signals - 《Getting Real》
- SQL 左外连接,右外连接,全连接,内连接 4种连接的区别
- xmu1246 素数筛选
- C#自己写的一个自定义分页控件
- memcpy() memmove() memccpy()
- C#委托
- 见到的最好的log4net说明