Oracle中的GROUP BY 子句

数据准备

-- 第一步：建表：
-- 删除表（包括数据结构）
drop table TMP_EMP;
drop table TMP_DEPT;
-- 删除表（不包括数据结构）
truncate table TMP_EMP;
truncate table TMP_DEPT;

create table TMP_DEPT(
       deptno number(10) primary key,
       dname varchar2(30),
       loc varchar2(30)
);

create table TMP_EMP(
       empno number(10) primary key,
       ename varchar2(30),
       sex varchar2(10),
       job varchar2(30),
       mgr varchar2(30),
       hiredate number(10),
       sal number(10),
       comm number(10),
       deptno number(10),
       foreign key(deptno) references TMP_DEPT(deptno)
)
;

-- 第二步：向各表中插入数据：

insert into TMP_DEPT values(1, '创意部门' ,'Pittsburgh');
insert into TMP_DEPT values(2, '客户关系部门' ,'Pittsburgh');
insert into TMP_DEPT values(3, '媒介部门' ,'Pittsburgh');
insert into TMP_DEPT values(4, '数据分析部门' ,'Pittsburgh');
insert into TMP_DEPT values(5, '财务部门' ,'Pittsburgh');

insert into TMP_EMP values(1, 'Brian','male', 'MANAGER' ,'Brian',20000214, 10000, 2000, 1);
insert into TMP_EMP values(2, 'Justin','male', 'INTERN' ,'Brian',20000214, 4000, 2000, 1);
insert into TMP_EMP values(3, 'Emmt','male', 'MANAGER' ,'Brian',20000214, 8000, 2000, 2 );
insert into TMP_EMP values(4, 'Ted','male', 'MANAGER' ,'Brian',20000214, 8000, 2000, 5);
insert into TMP_EMP values(5, 'Melanie','female', 'MANAGER' ,'Brian',20000214, 8000, 2000, 3);
insert into TMP_EMP values(6, 'Lindsay','female', 'CLERK' ,'Brian',20000214, 8000, 2000, 1);
insert into TMP_EMP values(7, 'Chandler','male', 'MANAGER' ,'Brian',20000214, 8000, 2000, 4);

insert into TMP_EMP values(8, 'Debbie','female', 'CLERK' ,'Emmt',20000214, 6000, 2000, 2 );
insert into TMP_EMP values(9, 'David','male', 'CLERK' ,'Melanie',20000214, 6000, 2000, 3);
insert into TMP_EMP values(10, 'Tom0','male', 'CLERK' ,'Chandler',20000214, 6000, 2000, 4);
insert into TMP_EMP values(11, 'Tom1','female', 'CLERK' ,'Emmt',20000214, 6000, 2000, 2);
insert into TMP_EMP values(12, 'Tom2','female', 'CLERK' ,'Ted',20000214, 6000, 2000, 5);

GROUP BY 子句

ROLLUP 子句

-- 查看各部门的各职位的平均薪水、薪水合计、人数
SELECT deptno,job,ROUND(AVG(sal),3) AVG_SAL,SUM(sal) TOL_SAL,COUNT(1) CNT
FROM TMP_EMP
GROUP BY ROLLUP(deptno,job)
;

结果

     DEPTNO JOB                               AVG_SAL    TOL_SAL        CNT
----------- ------------------------------ ---------- ---------- ----------
          1 CLERK                                8000       8000          1
          1 INTERN                               4000       4000          1
          1 MANAGER                             10000      10000          1
          1                                  7333.333      22000          3
          2 CLERK                                6000      12000          2
          2 MANAGER                              8000       8000          1
          2                                  6666.667      20000          3
          3 CLERK                                6000       6000          1
          3 MANAGER                              8000       8000          1
          3                                      7000      14000          2
          4 CLERK                                6000       6000          1
          4 MANAGER                              8000       8000          1
          4                                      7000      14000          2
          5 CLERK                                6000       6000          1
          5 MANAGER                              8000       8000          1
          5                                      7000      14000          2
                                                 7000      84000         12

17 rows selected

GROUPING SETS 子句

-- 查看各部门的平均薪水、薪水合计、人数,各职位的平均薪水、薪水合计、人数
SELECT deptno,job,ROUND(AVG(sal),3) AVG_SAL,SUM(sal) TOL_SAL,COUNT(1) CNT
FROM TMP_EMP
GROUP BY GROUPING SETS(deptno,job)
;

结果

     DEPTNO JOB                               AVG_SAL    TOL_SAL        CNT
----------- ------------------------------ ---------- ---------- ----------
          1                                  7333.333      22000          3
          2                                  6666.667      20000          3
          5                                      7000      14000          2
          4                                      7000      14000          2
          3                                      7000      14000          2
            CLERK                            6333.333      38000          6
            MANAGER                              8400      42000          5
            INTERN                               4000       4000          1

8 rows selected

CUBE子句

-- 计算各职位的男女的平均薪水、薪水合计、人数，各职位的平均薪水、薪水合计、人数,男女的平均薪水、薪水合计、人数，全公司的平均薪水、薪水合计、人数。
SELECT job,sex,ROUND(AVG(sal),3) AVG_SAL,SUM(sal) TOL_SAL,COUNT(1) CNT
FROM TMP_EMP
GROUP BY CUBE(job,sex)
ORDER BY job,sex
;

结果

JOB                            SEX           AVG_SAL    TOL_SAL        CNT
------------------------------ ---------- ---------- ---------- ----------
CLERK                          female           6500      26000          4
CLERK                          male             6000      12000          2
CLERK                                       6333.333      38000          6
INTERN                         male             4000       4000          1
INTERN                                          4000       4000          1
MANAGER                        female           8000       8000          1
MANAGER                        male             8500      34000          4
MANAGER                                         8400      42000          5
                               female           6800      34000          5
                               male         7142.857      50000          7
                                                7000      84000         12

11 rows selected

参考文章

Oracle Group by增强功能(Rollup，Cube，Grouping sets及Grouping函数)学习笔记+实例_oracle grouping优化-CSDN博客

Oracle group by 扩展函数详解（grouping sets、rollup、cube）_oracle group by grouping sets-CSDN博客