ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE制作表时的“小计”和“合计”

ORACLE制作表时的“小计”和“合计”

原创 Linux操作系统 作者:mahanso 时间:2013-08-08 10:27:38 0 删除 编辑

先来一段概念:

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

 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.      GROUPPINGROLLUP的基本知识

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统计出初步的数据集。

 

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

查询的结果如下表所示

烟叶等级

等级组别

原发件数

原发重量

验收重量

(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直接实现的方法

 

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

  

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

烟叶等级

等级组别

原发件数

原发重量

验收重量

(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中实现小计来得潇洒、简洁。



感谢作者,文章转自:http://www.cnblogs.com/hx8023zx/archive/2012/01/09/2317028.html


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12272958/viewspace-768010/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-18

  • 博文量
    160
  • 访问量
    598182