sql实现报表加小计、合计

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

 

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

 

 

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  

 

 

 

 

 

 

评论

1 楼 datawarehouse 2012-09-14  

很不错的方法