SCOTT:145@hongye > select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
例子1：普通的 Group by 子句
SCOTT:145@hongye > select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750
例子2：带有 rollup 函数的 Group by子句，可以返回rollup中指定列的总计信息
SCOTT:145@hongye > select deptno,sum(sal) from emp group by rollup(deptno);
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
29025
例子3：带有 rollup 函数的 Group by子句，
其中 rollup 带有两个参数，
那么先以第一个参数为准，返回第二个参数的小计信息，
然后在最后返回所有信息的总计信息
SCOTT:145@hongye > 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
13 rows selected.
例子4：带有 cube 函数的 Group by子句，
可以返回cube中指定的各个列的总计信息，并返回一个总计信息
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by cube(deptno,job) order by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
18 rows selected.
例子5：使用 grouping 函数判断哪一列的信息是属于 小计或总计的信息。
grouping(deptno)，若是deptno列中遇到小计的行信息，那么此行的 grouping(deptno) 显示为1
注意 else deptno||''中，必须要使用||''，否则会报错：
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
这是因为在这里必须使用字符串的变量，而deptno是number类型的，所以不能通过。
SCOTT:145@hongye > select case grouping(deptno) when 1 then 'SUM' else deptno||'' end as deptno,sum(sal) from emp group by rollup(deptno);
DEPTNO SUM(SAL)
---------------------------------------- ----------
10 8750
20 10875
30 9400
SUM 29025
同理：如果不使用 case when 语句而使用 decode，也必须使用 deptno||''
SCOTT:145@hongye > select decode(grouping(deptno),0,deptno||'','SUM'),sum(sal) from emp group by rollup(deptno);
DECODE(GROUPING(DEPTNO),0,DEPTNO||'','SU SUM(SAL)
---------------------------------------- ----------
10 8750
20 10875
30 9400
SUM 29025
例子6：使用 grouping sets 函数,在group by 语句中使用 grouping sets可以只返回小计信息
group by grouping sets(deptno,job)，则只返回了deptno和job列的小计信息。
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
30 9400
20 10875
10 8750
8 rows selected.
例子7：grouping_id函数的使用,grouping_id是grouping 的位向量
grouping_id(deptno,job)，此时：
如果grouping(deptno)=0，grouping_id(job)=0，则grouping_id=[00](2进制)=0(10进制)
如果grouping(deptno)=0，grouping_id(job)=1，则grouping_id=[01](2进制)=1(10进制)
如果grouping(deptno)=1，grouping_id(job)=0，则grouping_id=[10](2进制)=2(10进制)
如果grouping(deptno)=1，grouping_id(job)=1，则grouping_id=[11](2进制)=3(10进制)
SCOTT:145@hongye > select deptno,job,grouping(deptno) grp1,grouping(job) grp2,grouping_id(deptno,job) grp3,sum(sal) from emp group by cube(deptno,job) order by deptno,job;
DEPTNO JOB GRP1 GRP2 GRP3 SUM(SAL)
---------- --------- ---------- ---------- ---------- ----------
10 CLERK 0 0 0 1300
10 MANAGER 0 0 0 2450
10 PRESIDENT 0 0 0 5000
10 0 1 1 8750
20 ANALYST 0 0 0 6000
20 CLERK 0 0 0 1900
20 MANAGER 0 0 0 2975
20 0 1 1 10875
30 CLERK 0 0 0 950
30 MANAGER 0 0 0 2850
30 SALESMAN 0 0 0 5600
30 0 1 1 9400
ANALYST 1 0 2 6000
CLERK 1 0 2 4150
MANAGER 1 0 2 8275
PRESIDENT 1 0 2 5000
SALESMAN 1 0 2 5600
1 1 3 29025
18 rows selected.
例子8：使用grouping_id 和 cube 也可以只返回小计信息，只要加上条件 grouping_id>0 即可
但是此方法的效率不如 grouping sets函数。
SCOTT:145@hongye > select deptno,job,grouping(deptno) grp1,grouping(job) grp2,grouping_id(deptno,job) grp3,sum(sal) from emp group by cube(deptno,job) having grouping_id(deptno,job)>0 order by deptno,job;
DEPTNO JOB GRP1 GRP2 GRP3 SUM(SAL)
---------- --------- ---------- ---------- ---------- ----------
10 0 1 1 8750
20 0 1 1 10875
30 0 1 1 9400
ANALYST 1 0 2 6000
CLERK 1 0 2 4150
MANAGER 1 0 2 8275
PRESIDENT 1 0 2 5000
SALESMAN 1 0 2 5600
1 1 3 29025
9 rows selected.
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by grouping sets(deptno,job);
DEPTNO JOB SUM(SAL)
---------- --------- ----------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
30 9400
20 10875
10 8750
8 rows selected.
例子9：同一个列可以在 group by 子句中使用多次。
SCOTT:145@hongye > select deptno,job,sum(sal) from emp group by deptno,rollup(deptno,job) order by deptno,job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
10 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
30 9400
15 rows selected.
例子10：在例子9中，可以发现，每个小计的信息都是重复出现的，都出现了2遍，
可以使用group_id函数来消除此现象
SCOTT:145@hongye > select deptno,job,group_id(),sum(sal) from emp group by deptno,rollup(deptno,job) order by deptno,job;
DEPTNO JOB GROUP_ID() SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 0 1300
10 MANAGER 0 2450
10 PRESIDENT 0 5000
10 1 8750
10 0 8750
20 ANALYST 0 6000
20 CLERK 0 1900
20 MANAGER 0 2975
20 1 10875
20 0 10875
30 CLERK 0 950
30 MANAGER 0 2850
30 SALESMAN 0 5600
30 1 9400
30 0 9400
15 rows selected.
可以看出，每次重读，group_id 都会加1，如果一条记录重复了n遍，那么就会有0~(n-1)个不同的 group_id
使用 group_id=0 就可以剔除掉重复的记录
SCOTT:145@hongye > select deptno,job,group_id(),sum(sal) from emp group by deptno,rollup(deptno,job) having group_id()=0 order by deptno,job;
DEPTNO JOB GROUP_ID() SUM(SAL)
---------- --------- ---------- ----------
10 CLERK 0 1300
10 MANAGER 0 2450
10 PRESIDENT 0 5000
10 0 8750
20 ANALYST 0 6000
20 CLERK 0 1900
20 MANAGER 0 2975
20 0 10875
30 CLERK 0 950
30 MANAGER 0 2850
30 SALESMAN 0 5600
30 0 9400
12 rows selected.
SCOTT:145@hongye >
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24465008/viewspace-689976/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
