-
对GROUP BY的增强
- 使用ROLLUP操作产生求和值
- 使用CUBE操作产生
- 使用GROUPING函数确定该行值是由ROLLUP或者CUBE创建的
- 使用GROUPING SETS生成一个简单的结果集
- [oracle@oracle-db-19c ~]$
- [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
- SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 10:19:05 2022
- Version 19.3.0.0.0
- Copyright (c) 1982, 2019, Oracle. All rights reserved.
- Connected to:
- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
- Version 19.3.0.0.0
- SQL>
- SQL> alter session set container=PDB1;
- Session altered.
- SQL> show con_name
- CON_NAME
- ------------------------------
- PDB1
- SQL>
- SQL>
- SQL> conn scott/tiger@PDB1
- Connected.
- SQL>
- SQL> show user
- USER is "SCOTT"
- SQL>
- SQL>
- SQL> set pagesize 200
- SQL> set linesize 200
- SQL> select * from emp;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
- ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
- 7369 SMITH CLERK 7902 17-DEC-80 800 20
- 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
- 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
- 7566 JONES MANAGER 7839 02-APR-81 2975 20
- 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
- 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
- 7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
- 7839 KING PRESIDENT 17-NOV-81 5000 10
- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
- 7876 ADAMS CLERK 7788 02-APR-87 1100 20
- 7900 JAMES CLERK 7698 03-DEC-81 950 30
- 7902 FORD ANALYST 7566 03-DEC-81 3000 20
- 7934 MILLER CLERK 7782 23-JAN-82 1300 10
- 14 rows selected.
- SQL> select deptno,sum(sal) from emp group by deptno;
- DEPTNO SUM(SAL)
- ---------- ----------
- 30 9400
- 10 8750
- 20 10875
- SQL> select deptno,sum(sal) from emp having sum(sal)>9000 group by deptno;
- DEPTNO SUM(SAL)
- ---------- ----------
- 30 9400
- 20 10875
- SQL> select deptno,job,sum(sal) from emp group by deptno,job;
- DEPTNO JOB SUM(SAL)
- ---------- --------- ----------
- 20 MANAGER 2975
- 20 ANALYST 6000
- 10 PRESIDENT 5000
- 10 CLERK 1300
- 30 SALESMAN 5600
- 10 MANAGER 2450
- 20 CLERK 1900
- 30 MANAGER 2850
- 30 CLERK 950
- 9 rows selected.
- SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1,2;
- 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
- 9 rows selected.
- SQL>
带有ROLLUP和CUBE运算符的GROUP BY
- 带有ROLLUP或者CUBE的GROUP BY 会通过交叉参考列产生一个超级总计行
- ROLLUP分组生成一个结果集，包含有通常的分组行和求和值
- CUBE分组生成一个结果集，包含从ROLLUP里面的行和交叉表行。
- SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job) order by 1,2;
- 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
- 29025
- 13 rows selected.
- SQL>
- SQL> select deptno,job,sum(sal) from emp group by deptno,job
- 2 union all
- 3 select deptno,null,sum(sal) from emp group by deptno
- 4 union all
- 5 select null,null,sum(sal) from emp;
- DEPTNO JOB SUM(SAL)
- ---------- --------- ----------
- 20 MANAGER 2975
- 20 ANALYST 6000
- 10 PRESIDENT 5000
- 10 CLERK 1300
- 30 SALESMAN 5600
- 10 MANAGER 2450
- 20 CLERK 1900
- 30 MANAGER 2850
- 30 CLERK 950
- 30 9400
- 10 8750
- 20 10875
- 29025
- 13 rows selected.
- SQL>
rollup(a,b,c)
group by a
union all
group by a,b
union all
group by a,b,c
union all
total
cube(a,b,c)
group by aunion all
group by b
union all
group by c
union all
group by a,b
union all
group by a,c
union all
group by b,c
union all
group by a,b,c
union all
total
- SQL>
- SQL> select deptno,job,sum(sal) from emp group by deptno,job
- 2 union all
- 3 select deptno,null,sum(sal) from emp group by deptno
- 4 union all
- 5 select null,null,sum(sal) from emp;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1761893045
- ----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 15 | 190 | 11 (19)| 00:00:01 |
- | 1 | UNION-ALL | | | | | |
- | 2 | HASH GROUP BY | | 11 | 165 | 4 (25)| 00:00:01 |
- | 3 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)| 00:00:01 |
- | 4 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
- | 5 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
- | 6 | SORT AGGREGATE | | 1 | 4 | | |
- | 7 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------
- SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job) order by 1,2;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 52302870
- -----------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 11 | 165 | 4 (25)| 00:00:01 |
- | 1 | SORT GROUP BY ROLLUP| | 11 | 165 | 4 (25)| 00:00:01 |
- | 2 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
- -----------------------------------------------------------------------------
- SQL>
GROUPING函数
- SQL>
- SQL> select deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job);
- DEPTNO JOB SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
- ---------- --------- ---------- ---------------- -------------
- 10 CLERK 1300 0 0
- 10 MANAGER 2450 0 0
- 10 PRESIDENT 5000 0 0
- 10 8750 0 1
- 20 CLERK 1900 0 0
- 20 ANALYST 6000 0 0
- 20 MANAGER 2975 0 0
- 20 10875 0 1
- 30 CLERK 950 0 0
- 30 MANAGER 2850 0 0
- 30 SALESMAN 5600 0 0
- 30 9400 0 1
- 29025 1 1
- 13 rows selected.
- SQL>
- SQL> select decode(grouping(deptno)||grouping(job),'01','Subtotal ' || deptno,'11','Total',deptno) deptno,job,sum(sal) from emp group by rollup(deptno,job);
- DEPTNO JOB SUM(SAL)
- ------------------------------------------------- --------- ----------
- 10 CLERK 1300
- 10 MANAGER 2450
- 10 PRESIDENT 5000
- Subtotal 10 8750
- 20 CLERK 1900
- 20 ANALYST 6000
- 20 MANAGER 2975
- Subtotal 20 10875
- 30 CLERK 950
- 30 MANAGER 2850
- 30 SALESMAN 5600
- Subtotal 30 9400
- Total 29025
- 13 rows selected.
- SQL>
- SQL> select decode(grouping(deptno)||grouping(job),'01','Subtotal ' || deptno,'11','Total',deptno) deptno,job,sum(sal),grouping(deptno),grouping(job) from emp group by rollup(deptno,job);
- DEPTNO JOB SUM(SAL) GROUPING(DEPTNO) GROUPING(JOB)
- ------------------------------------------------- --------- ---------- ---------------- -------------
- 10 CLERK 1300 0 0
- 10 MANAGER 2450 0 0
- 10 PRESIDENT 5000 0 0
- Subtotal 10 8750 0 1
- 20 CLERK 1900 0 0
- 20 ANALYST 6000 0 0
- 20 MANAGER 2975 0 0
- Subtotal 20 10875 0 1
- 30 CLERK 950 0 0
- 30 MANAGER 2850 0 0
- 30 SALESMAN 5600 0 0
- Subtotal 30 9400 0 1
- Total 29025 1 1
- 13 rows selected.
- SQL>
-
相关阅读:
向量数据库——AI时代的基座
dom的基础
大学生书店系统
NVIDIA CX 网卡驱动安装 测试
根据模板动态生成word（一）使用freemarker生成word
研究生综合英语上第五单元
简介性能测试
231.2的幂
【FI】FB02中Coding Block字段如何设置为可修改
信号与系统分析《1》
- 原文地址：https://blog.csdn.net/u011868279/article/details/127917230
