ORACLE
用 SQL 实现统计报表中的“小计”和“合计”
转载地址：http://www.cnblogs.com/hx8023zx/archive/2012/01/09/2317028.html
先来一段概念:
----------------------
DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。DECODE有什么用途 呢？ 先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录 中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。 如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。
--------------------------
在 开发统计报表的过程中，经常会碰到在查询到的数据集中，插入一些小计行和合计行。比如在烤烟系统中，几乎每个统计报表都需要加入“合计”行，还有不少涉及 到烟叶等级的统计报表需要加入各烟叶等级的小计行。我看到不少人（包括我自己）都是在程序中专门写一些方法来处理的，有的方法还很繁琐。最近在看 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 Group by CUBE 和 ROLLUP 之间的区别
转载地址：http://blog.sina.com.cn/s/blog_4b02ab3b01011o42.html
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
例如，简单表 Inventory 中包含：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查询将生成小计报表：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL '
ELSE ISNULL(Item, 'UNKNOWN ')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL '
ELSE ISNULL(Color, 'UNKNOWN ')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00
(7 row(s) affected)
如果查询中的 ROLLUP 关键字更改为 CUBE，那么 CUBE 结果集与上述结果相同，只是在结果集的末尾还会返回下列两行：
ALL Blue 225.00
ALL Red 433.00
3241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
