oracle通过分组复杂,谁能给个例子有case(when ....) group by where and 这种组合起来比较复杂的分组...

select *

from (select case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

0

when grouping(fs_batchno) = 1 and grouping(fs_steeltype) = 1 and

grouping(fs_spec) = 0 then

1

when grouping(fs_batchno) = 1 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 1 then

2

when grouping(fs_batchno) = 1 and grouping(fs_steeltype) = 1 and

grouping(fs_spec) = 1 then

3

else

4

end XH,

case

when grouping(fs_batchno) = 1 and grouping(fs_steeltype) = 1 and

grouping(fs_spec) = 0 then

'规格【' || fs_spec || '】小计：'

when grouping(fs_batchno) = 1 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 1 then

'牌号【' || fs_steeltype || '】小计：'

when grouping(fs_batchno) = 1 and grouping(fs_steeltype) = 1 and

grouping(fs_spec) = 1 then

'总计：'

when grouping(fs_steeltype) = 0 and grouping(fs_spec) = 0 then

max(fs_batchno)

end fs_batchno,

case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

fs_steeltype

else

null

end fs_steeltype,

case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

fs_spec

else

null

end fs_spec,

sum(nvl(fn_bandcount, 0)) fn_bandcount,

sum(nvl(fn_totalweight, 0)) fn_totalweight,

case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

max(fs_productno)

else

null

end fs_productno,

case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

max(fd_starttime)

else

null

end fd_starttime,

case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

max(fd_endtime)

else

null

end fd_endtime,

case

when grouping(fs_batchno) = 0 and grouping(fs_steeltype) = 0 and

grouping(fs_spec) = 0 then

max(fs_completeflag)

else

null

end fs_completeflag

from (select t.fs_batchno,

t.fs_productno,

t.fs_steeltype,

t.fs_spec,

t.fn_bandcount,

t.fn_totalweight,

to_char(t.fd_starttime, 'yyyy-MM-dd hh24:mi:ss') fd_starttime,

to_char(t.fd_endtime, 'yyyy-MM-dd hh24:mi:ss') fd_endtime,

decode(t.fs_completeflag, '1', '√', '') fs_completeflag

from dt_gx_storageweightmain t

where 1 = 1

and t.fd_starttime between

to_date('2012-04-01 00:00', 'yyyy-MM-dd HH24:mi') and

to_date('2012-04-09 23:59', 'yyyy-MM-dd HH24:mi'))

group by cube(fs_batchno, fs_steeltype, fs_spec))

where xh <> 4

order by xh, fs_batchno