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的情况
