Oracle grouping和rollup简单测试
SQL> select department_id,sum(salary) from employees where department_id in(10,30,90,100) group by department_id order by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
30 24900
90 58000
100 51608 SQL> select department_id,first_name,sum(salary) from employees where department_id in(10,30,90,100) group by (department_id,first_name) order by department_id;
DEPARTMENT_ID FIRST_NAME SUM(SALARY)
------------- -------------------- -----------
10 Jennifer 4400
30 Alexander 3100
30 Den 11000
30 Guy 2600
30 Karen 2500
30 Shelli 2900
30 Sigal 2800
90 Lex 17000
90 Neena 17000
90 Steven 24000
100 Daniel 9000
100 Ismael 7700
100 John 8200
100 Jose Manuel 7800
100 Luis 6900
100 Nancy 12008
16 rows selected SQL> select department_id,first_name,sum(salary) from employees where department_id in(10,30,90,100) group by rollup(department_id,first_name) order by department_id;
DEPARTMENT_ID FIRST_NAME SUM(SALARY)
------------- -------------------- -----------
10 Jennifer 4400
10 4400
30 Alexander 3100
30 Den 11000
30 Guy 2600
30 Karen 2500
30 Shelli 2900
30 Sigal 2800
30 24900
90 Lex 17000
90 Neena 17000
90 Steven 24000
90 58000
100 Daniel 9000
100 Ismael 7700
100 John 8200
100 Jose Manuel 7800
100 Luis 6900
100 Nancy 12008
100 51608
DEPARTMENT_ID FIRST_NAME SUM(SALARY)
------------- -------------------- -----------
138908
21 rows selected SQL> select department_id,grouping(department_id),first_name,grouping(first_name),sum(salary) from employees where department_id in(10,30,90,100) group by rollup(department_id,first_name) order by department_id;
DEPARTMENT_ID GROUPING(DEPARTMENT_ID) FIRST_NAME GROUPING(FIRST_NAME) SUM(SALARY)
------------- ----------------------- -------------------- -------------------- -----------
10 0 Jennifer 0 4400
10 0 1 4400
30 0 Alexander 0 3100
30 0 Den 0 11000
30 0 Guy 0 2600
30 0 Karen 0 2500
30 0 Shelli 0 2900
30 0 Sigal 0 2800
30 0 1 24900
90 0 Lex 0 17000
90 0 Neena 0 17000
90 0 Steven 0 24000
90 0 1 58000
100 0 Daniel 0 9000
100 0 Ismael 0 7700
100 0 John 0 8200
100 0 Jose Manuel 0 7800
100 0 Luis 0 6900
100 0 Nancy 0 12008
100 0 1 51608
DEPARTMENT_ID GROUPING(DEPARTMENT_ID) FIRST_NAME GROUPING(FIRST_NAME) SUM(SALARY)
------------- ----------------------- -------------------- -------------------- -----------
1 1 138908
21 rows selected
rollup为按分组统计小计和。
grouping(department_id)和grouping(first_name)
如果当前列所在的行为空，则显示为1，不为空则显示为0；