Oracle 使用分析函数实现小计合计

select grouping(vsaltype) as sq,

                         vsaltype || '小计计' vsaltype,

                         sum(amount) as amount,

                                   '' vvin,

                                    '' VPROPERTYWH,

                               '' VPROPERTYWHDESC

                              fromSPTW90_INVENTORY_NCS_TMP

                               group by rollup(vsaltype)

得到结果为:

:

分析结论:

Grouping(上卷字段)两种情况:

SQ为0情况:只是按照vsaltype进行group by

SQ为1情况:把小计情况进行一次汇总,即别文写的 group by  rollup(A,B,C)的流程是group by (A,B,C)->

group by (A,B) ->group by (A)-> 全表,本例只是执行后两句

  select -1 as sq,

                 vsaltype,

                 amount,

                 vvin,

               VPROPERTYWH,

                 VPROPERTYWHDESC

                  

  from SPTW90_INVENTORY_NCS_TMP

得到结果为:


分析结论:得到所有明细数据,并赋一个新的虚拟字段sq 并设sq为-1

这样则:

select sq, vsaltype, amount, vvin, VPROPERTYWH,VPROPERTYWHDESC

 from (select sq, vsaltype, amount, vvin, VPROPERTYWH, VPROPERTYWHDESC

         from (select grouping(vsaltype) as sq,

                       vsaltype || '小计计' vsaltype,

                       sum(amount) as amount,

                       '' vvin,

                       '' VPROPERTYWH,

                       '' VPROPERTYWHDESC

                  from SPTW90_INVENTORY_NCS_TMP

                 group by rollup(vsaltype)

                union all

                select -1 as sq,

                       vsaltype,

                       amount,

                       vvin,

                       VPROPERTYWH,

                       VPROPERTYWHDESC

                  from SPTW90_INVENTORY_NCS_TMP

                 where 1 = 1) g

        where g.sq <> 1

        order by vsaltype, sq)

把sq为0,1行数与sq为-1行数进行union all

 

在where条件进行限定,取出sq非1的数据即sq<>1

So,取最终合计的话只需取出sq =1的情况

最终模型应该是这种效果(从Oracle中导出的Excel):

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值