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

用SQL实现统计报表中的“小计”和“合计”先来一段概念:-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.ROLLUPROLLUP运算符生成聚合汇总,需要汇总信息时，此运算很有用。该运算符生成的结果集类似于CUBE运算符生成的结果集。但它们两者有一些区别，CUBE生成的结果集显示了所选列中值的所有组合的聚合。而ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合。语法:ROLLUP(column_name1，column_name2。)用法:用在GROUP BY子句中。对那些需要按其分组，并对其分组的聚合数据进行汇总的列，就请对这些列加上ROLLUP运算符。注意:“GROUP BYROLLUP(col1,col2)”与“GROUP BYROLLUP(col1),ROLLUP(col2)”是有区别的。“GROUP BYROLLUP(col1),ROLLUP(col2)”其实就相当于“GROUP BYCUBE(col1,col2)”，因为它对每个分组的聚合都要进行汇总。“GROUP BYROLLUP(col1,col2)”与“GROUP BYROLLUP(col2,col1)”也有区别，前者是对每个col1的唯一值都产生一个在col1下各个col2聚合值汇总的行，再对所有col1与col2的聚合值产生一个汇总行；而后者是对每个col2的唯一值都产生一个在col2下各个col1聚合值的汇总行，再对所有col1与col2的聚合值产生一个汇总行。这样说逻辑可能不太清晰，我们看一下下面的图表就一目了然了。col1col2amountcol1col2amount323584777323584777331277487542200789.13163596525227443242200789.12405999843861949833127748754882028843861949852274432532024463532024463323418837522988952747883527478835GROUP 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 T4WHERE 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-DDGROUP BY T4.TOBACCO_CLASS_TYPE,T1.TOBACCO_CLASS_NAMEORDER BY T4.TOBACCO_CLASS_TYPE查询的结果如下表所示烟叶等级等级组别原发件数原发重量验收重量(B1F)上桔一514945197800197508.1(B2F)上桔二518335333400332316.9(C1F)中桔一516942776027610.54(C2F)中桔二518033212031650.4(C3F)中桔三516381255240255372.6(X1F)下桔一517530003012.2(B3F)上桔三524701188040187389.9(B4F)上桔四5212248804866.81(C3V)中微青三5217469606934.06(C4F)中桔四524639185560185276.4(X2F)下桔二5217396956069029.79(X2V)下微青二522610401038.34(X3F)下桔三5212635052050439.86(X4F)下桔四5310240804075.62(B3K)上杂三5400249.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_AMOUNTFROMVI_FK_BALANCE_DETAIL T1, TB_TOBACCO_CLASS T4WHERE 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-DDGROUP BY ROLLUP( T4.TOBACCO_CLASS_TYPE,T1.TOBACCO_CLASS_NAME)ORDER BY T4.TOBACCO_CLASS_TYPE通过查询得到统计结果如下表所示，该表的统计结果已经满足了统计要求。烟叶等级等级组别原发件数原发重量验收重量(B1F)上桔一514945197800197508.1(B2F)上桔二518335333400332316.9(C1F)中桔一516942776027610.54(C2F)中桔二518033212031650.4(C3F)中桔三516381255240255372.6(X1F)下桔一517530003012.2上等烟小计5121233849320847470.8(B3F)上桔三524701188040187389.9(B4F)上桔四5212248804866.81(C3V)中微青三5217469606934.06(C4F)中桔四524639185560185276.4(X2F)下桔二5217396956069029.79(X2V)下微青二522610401038.34(X3F)下桔三5212635052050