【转】ORACLE制作表时的“小计”和“合计” （ROLLUP）

先来一段概念:


----------------------


 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的聚合值产生一个汇总行。这样说逻辑可能不太清晰，我们看一下下面的图表就一目了然了。





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
  


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

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