参考博客:http://blog.itpub.net/519536/viewspace-610995/
表的初始数据:
使用rollup进行汇总之后的数据:
select t.first_name,sum(t.salary)
from t_test1 t
group by rollup(t.first_name)
可以改写为:
select nvl(t.first_name,'total'),sum(t.salary)
from t_test1 t
group by rollup(t.first_name)
使用group by实现roll up
select ta.first_name, sum(ta.salary) as salary
from t_test1 ta
group by ta.first_name
union all
select 'total', sum(tb.salary) as salary
from t_test1 tb
用多列rollup操作
select tt.first_name, tt.manager_id, sum(tt.salary)
from t_test2 tt
group by rollup(tt.first_name, tt.manager_id)
使用group by代替实现
select tt.first_name, tt.manager_id, sum(tt.salary)
from t_test2 tt
group by tt.first_name, tt.manager_id
union all
select tt.first_name, null, sum(tt.salary)
from t_test2 tt
group by tt.first_name
union all
select null, null, sum(tt.salary)
from t_test2 tt
select
tt.first_name,
tt.manager_id,
grouping(tt.first_name),
grouping(tt.manager_id),
sum(tt.salary)
from t_test2 tt
group by
rollup(tt.first_name,tt.manager_id)
如果显示“1”表示GROUPING函数对应的列(例如JOB字段)是由于ROLLUP函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。
如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。