數據準備 -- 第一步：建表： -- 刪除表（包括數據結構） drop table TMP_EMP; drop table TMP_DEPT; -- 刪除表（不包括數據結構） truncate table TMP_EMP; truncate table TMP_DEPT; create table ...
數據準備
-- 第一步：建表：
-- 刪除表（包括數據結構）
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博客
