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博客