rollup和grouping使用

1、创建表

create table test_rollup
(
type_name varchar2(10),
type_value number
);

2、插入数据结果

select * from test_rollup;
a	123
a	423
a	523
b	223
b	283
c	103
c	843
c	899
c	100

3、使用rollup函数

select type_name, sum(type_value)
  from test_rollup
 group by rollup(type_name);
a	1069
b	506
c	1945
	3520

4、使用rollup和grouping函数

select type_name, sum(type_value), grouping(type_name)
  from test_rollup
 group by rollup(type_name);
a	1069	0
b	506	0
c	1945	0
	3520	1

5、显示统计信息

select decode(grouping(type_name), 0, type_name, '总计'), sum(type_value)
  from test_rollup
 group by rollup(type_name);
a	1069
b	506
c	1945
总计	3520