【转】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 运算符⽣成聚合汇总, 需要汇总信息时，此运算很有⽤。该运算符⽣成的结果集类似于 CUB
微博背景E 运算符⽣成的结果集。但它们两者有⼀些区别，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中实现⼩计来得潇洒、简洁。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论