ORACLE中扩展的group by

扩展的GROUP BY
所谓的扩展的GROUP BY就是使用了分析函数rollup() 和 cube()。

rollup()：使分组结果中包含小计及总计信息，并可以传入多列字段
cube()：对做为参数传入的每一列都进行小计，多列的时候能显示优势。
例：
select id,sum(mount) from tt group by rollup(id);
ID SUM(MOUNT)
---------- ----------
1 60
2 150
3 240
4 210
5 440
1100
select id,sum(mount) from tt group by cube(id);
ID SUM(MOUNT)
---------- ----------
1100
1 60
2 150
3 240
4 210
5 440

或者,用nulls 显示指定空值的首尾位置:
select id,sum(mount) from tt group by cube(id) order by id nulls last;
注释:这里必须要有order by 才能使用nulls last，而且如果group by后面是id的时候nulls last/first 才有效果，如果是sum(mount)则没有效果.

SQL> select id,sum(mount) from tt group by rollup(id) order by id desc nulls first ;
ID SUM(MOUNT)
---------- ----------
1100
5 440
4 210
3 240
2 150
1 60
SQL> select id,sum(mount) from tt group by rollup(id) order by id desc nulls last ;
ID SUM(MOUNT)
---------- ----------
5 440
4 210
3 240
2 150
1 60
1100
SQL> select id,sum(mount) from tt group by rollup(id) order by sum(mount) desc nulls last ; //这里便看不到想要的效果
ID SUM(MOUNT)
---------- ----------
1100
5 440
3 240
4 210
2 150
1 60

下面是一些与rollup、cube联合使用的函数：
groupping():只能在有rollup或者cube的语句中使用，判断当前行是否是小计或者总计行（实质是通过是否列为空来判断）。比如，小计行的name列为空（即小计行），则返回1，不为空则返回0；
SQL> select id,sum(mount),grouping(id) from tt group by rollup(id) order by id desc nulls first ;
ID SUM(MOUNT) GROUPING(ID)
---------- ---------- ------------
1100 1
5 440 0
4 210 0
3 240 0
2 150 0
1 60 0
SQL> select case grouping(id) when 1 then 'zj' else id end as id,sum(mount) from tt group by cube(id) order by id desc;
ID SUM(MOUNT)
---- ----------
zj 1100
5 440
4 210
3 240
2 150
1 60

grouping_id(col1,col2)计算位向量，如例子中deptno和job在grouping的时候都返回1，则 grouping_id(deptno,job)结果则为二进制11，即3。job为空，返回0，而deptno返回1，则10，即2; （可以多列）
例：
SQL> select deptno,job,sum(sal) ,grouping(deptno) a,grouping(job) b,grouping_id(deptno,job) c from emp group by cube(deptno,job) order by deptno;

DEPTNO JOB SUM(SAL) A B C
------ --------- ---------- ---------- ---------- ----------
10 CLERK 1300 0 0 0
10 MANAGER 2450 0 0 0
10 PRESIDENT 5000 0 0 0
10 8750 0 1 1
20 ANALYST 6000 0 0 0
20 CLERK 1900 0 0 0
20 MANAGER 2975 0 0 0
20 10875 0 1 1
30 CLERK 950 0 0 0
30 MANAGER 2850 0 0 0
30 SALESMAN 5600 0 0 0
30 9400 0 1 1
ANALYST 6000 1 0 2
CLERK 4150 1 0 2
MANAGER 8275 1 0 2
PRESIDENT 5000 1 0 2
SALESMAN 5600 1 0 2
29025 1 1 3
它常与having联系使用，来筛选结果中的小计或总计的行。其实只需要grouping_id行大于零的都是。
SQL> select deptno,job,sum(sal) ,grouping(deptno) a,grouping(job) b,grouping_id(deptno,job) c from emp group by cube(deptno,job) having grouping_id(deptno,job)>0 order by deptno;
DEPTNO JOB SUM(SAL) A B C
------ --------- ---------- ---------- ---------- ----------
10 8750 0 1 1
20 10875 0 1 1
30 9400 0 1 1
ANALYST 6000 1 0 2
CLERK 4150 1 0 2
MANAGER 8275 1 0 2
PRESIDENT 5000 1 0 2
SALESMAN 5600 1 0 2
29025 1 1 3
注释：这样的结果只是比grouping sets子句多了一个最后的总计。

group_id()控制重复值，记录第一次出现时为0，第二次出现为1,第三次出现为2。
它不接受任何参数。
注释： group by语句可以同时使用普通列、rollup()和cube()等作为条件，如果一个列在不同地方出现了多次，结果集可能会重复。同时使用的含义是：普通列加上高级函数的每个结果作为group by的条件汇总，

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
SQL> select deptno,job,sum(sal) from emp group by deptno,rollup(deptno,job);
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
10 8750
20 10875
30 9400
10 8750
20 10875
30 9400
SQL> select deptno,job,sum(sal) ,group_id() from emp group by deptno,rollup(deptno,job);

DEPTNO JOB SUM(SAL) GROUP_ID()
------ --------- ---------- ----------
10 CLERK 1300 0
10 MANAGER 2450 0
10 PRESIDENT 5000 0
20 CLERK 1900 0
20 ANALYST 6000 0
20 MANAGER 2975 0
30 CLERK 950 0
30 MANAGER 2850 0
30 SALESMAN 5600 0
10 8750 0
20 10875 0
30 9400 0
10 8750 1
20 10875 1
30 9400 1
SQL> select deptno,job,sum(sal) ,group_id() from emp group by deptno,rollup(deptno,job) having group_id()=0;

grouping sets子句：
group by后带grouping sets子句就是只返回按单个列分组后的统计数据，不返回多个列组合分组的统计数据。
例：Group by grouping sets(A ,B)
产生的分组种数：2种；
第一种：group by A
第二种：group by B

SQL> select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
SQL> select deptno,job,sum(sal) from emp group by grouping sets(deptno,job) order by deptno;
DEPTNO JOB SUM(SAL)
------ --------- ----------
10 8750
20 10875
30 9400
ANALYST 6000
PRESIDENT 5000
SALESMAN 5600
CLERK 4150
MANAGER 8275

本文出自 “liord府邸” 博客，请务必保留此出处http://liord.blog.51cto.com/956684/940363

秒客网

ORACLE中扩展的group by

相关文章