select * from (select null,time,'avg',avg(value) from table group by time union all select * from table ) t order by t.time,type
with tab_test as (select '1' as time, 'aaa' as type, 11 as value from dual union all select '1' as time, 'bbbb' as type, 12 as value from dual union all select '1' as time, 'cccc' as type, 13 as value from dual union all select '2' as time, 'aaa' as type, 12 as value from dual union all select '2' as time, 'bbb' as type, 14 as value from dual union all select '2' as time, 'ccc' as type, 34 as value from dual)SELECT decode(grouping_id(time, type), 2, '小计', 3, '合计', time) as time, decode(grouping_id(time, type), 1, '小计', 3, '合计', type) as type, avg(value) FROM tab_test GROUP BY ROLLUP(time, type)
1月 类型2 值
1月 类型3 值
1月 平均值 值
2月 类型1 值
2月 类型2 值
2月 类型3 值
2月 平均值 值
(select null,time,'avg',avg(value) from table group by time
union all
select * from table
) t order by t.time,type
with tab_test as
(select '1' as time, 'aaa' as type, 11 as value
from dual
union all
select '1' as time, 'bbbb' as type, 12 as value
from dual
union all
select '1' as time, 'cccc' as type, 13 as value
from dual
union all
select '2' as time, 'aaa' as type, 12 as value
from dual
union all
select '2' as time, 'bbb' as type, 14 as value
from dual
union all
select '2' as time, 'ccc' as type, 34 as value from dual)SELECT decode(grouping_id(time, type), 2, '小计', 3, '合计', time) as time,
decode(grouping_id(time, type), 1, '小计', 3, '合计', type) as type,
avg(value)
FROM tab_test
GROUP BY ROLLUP(time, type)