http://blog.itpub.net/31369373/viewspace-2125727/
![](http://blog.itpub.net/attachment/201609/29/31369373_1475110821y5kO.png)
2 我们先给它加上一个 row_number() over (order by 1) AS rowno 最为一个唯一值
![](http://blog.itpub.net/attachment/201609/29/31369373_1475112133ssKs.png)
3 然后再 用group by rollup 来分组和统计
![](http://blog.itpub.net/attachment/201609/29/31369373_1475112768LNnB.png)
4 这样你会发现有两个合计 ,我们可以去掉第二个 ,把 第二个合计 变为 null 就可以了
5 假如你要下面圈圈的不合计,只想合计最后一列 refund_amount 的话
![](http://blog.itpub.net/attachment/201609/29/31369373_1475113059Vply.png)
你的 查询字段部分可以这么写 :
SELECT decode(grouping(rowno),1,'合计',MAX(service_name)) AS service_name,
decode(grouping(rowno),1,null,SUM(supply_price)) AS supply_price ,
decode(grouping(rowno),1,null,SUM(no2_price)) AS no2_price,
decode(grouping(rowno),1,null,MAX(refund_type) ) AS refund_type,
SUM(refund_amount) AS refund_amount
FROM .....................................
6 本来这样是可以完成的,但是你后面假如出现 有一列 有这种情况的,就是数字列本来是数字型的,后面被你替换成 null了,默认成了 字符型,会出现如下的情况:是数字的时候,并且小数点前面的个 0 的时候,字符型通常会变成 .01 这种格式,那时候我们可以吧null 用函数 to_number 一下,变成 数字型:
![](http://blog.itpub.net/attachment/201609/29/31369373_1475113482yx84.png)
to_number 后 变成如下:
![](http://blog.itpub.net/attachment/201609/29/31369373_14751135512022.png)
总体脚本:
SELECT decode(grouping(rowno),1,'合计',MAX(service_name)) AS service_name,
decode(grouping(rowno),1,to_number(null),SUM(supply_price)) AS supply_price ,
decode(grouping(rowno),1,to_number(null),SUM(no2_price)) AS no2_price,
decode(grouping(rowno),1,to_number(null),MAX(refund_type) ) AS refund_type,
SUM(refund_amount) AS refund_amount
FROM
(SELECT decode(b.type,'1','漏发','2','已订未到货','3','破损','4','剩余库存退还','5','其他原因',b.type) refund_type,
b.service_name,
sp.supply_price,
sp.no2_price,
b.refund_amount,
row_number() over (order by 1) AS rowno
FROM web114_erp_refund_batch b,
web114_bmaccount_dimension d,
web114_lineshop_product t,
t_goods_spec sp
WHERE b.shop_code =d.shop_code
AND b.shop_code =t.shop_code
AND b.service_id =t.service_id
AND b.service_id =sp.service_type_second_id
AND t.good_id =sp.good_id
AND b.create_time >=to_date('2016-08-20','yyyy-mm-dd')
AND b.create_time <to_date('2016-09-28','yyyy-mm-dd')) A
GROUP BY ROLLUP(rowno)
decode(grouping(rowno),1,to_number(null),SUM(supply_price)) AS supply_price ,
decode(grouping(rowno),1,to_number(null),SUM(no2_price)) AS no2_price,
decode(grouping(rowno),1,to_number(null),MAX(refund_type) ) AS refund_type,
SUM(refund_amount) AS refund_amount
FROM
(SELECT decode(b.type,'1','漏发','2','已订未到货','3','破损','4','剩余库存退还','5','其他原因',b.type) refund_type,
b.service_name,
sp.supply_price,
sp.no2_price,
b.refund_amount,
row_number() over (order by 1) AS rowno
FROM web114_erp_refund_batch b,
web114_bmaccount_dimension d,
web114_lineshop_product t,
t_goods_spec sp
WHERE b.shop_code =d.shop_code
AND b.shop_code =t.shop_code
AND b.service_id =t.service_id
AND b.service_id =sp.service_type_second_id
AND t.good_id =sp.good_id
AND b.create_time >=to_date('2016-08-20','yyyy-mm-dd')
AND b.create_time <to_date('2016-09-28','yyyy-mm-dd')) A
GROUP BY ROLLUP(rowno)
7 至此 ,我们就完成了 用group by rollup 和 grouping 来解决 用在 最后一行加合计的结果 ,而不用union all
8 这边解释下 grouping 在这里用法的意思:
GROUPING函数可以接受一列,返回0或者1。如果列值为空,那么GROUPING()返回1;如果列值非空,那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时,GROUPING()就非常有用
也就是 如果 这一行不是数据本身的行,而是数据库帮你无中生有的一行 ,
1就表示是数据库生成的,第16条数据是数据库生成的,所以返回1 ;0表示你自己的数据 ,1到15条数据是能查出来的数据,也就是
非空
![](http://blog.itpub.net/attachment/201609/29/31369373_14751141133Blu.png)
而 ROLLUP,是GROUP BY子句的一种扩展,可以为每个分组返回小计记录以及为所有分组返回总计记录。
这个 大家可以看这个链接链接的例子,或者去网上找找,看是怎么运用的:
http://blog.csdn.net/wanghai__/article/details/4817920
这个 是 grouping 的用法介绍
http://millerrch.iteye.com/blog/1882423