1.组及组函数回顾
SELECT [column,]group_function(column)...
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
例1:计算工作岗位以CL开头的各部门雇员的平均薪水、有佣金收入的雇员数和最晚受雇日期
SELECT AVG(sal),COUNT(comm),MAX(hiredate)
FROM emp
WHERE job LIKE 'CL%';
例2:查询emp表,按照部门编号和工作岗位分组,显示部门编号、工作岗位、薪水合计、有佣金收入的雇员数
SELECT deptno,job,SUM(sal),COUNT(comm)
FROM emp
GROUP BY deptno,job;
2.HAVING子句的回顾
SELECT [column,]group_function(column)...
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING having_expression]
[ORDER BY column] ;
例:查询emp表,按照部门编号进行分组,显示平均薪水高于$2000的部门编号和平均薪水
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;
3.ROLLUP操作
SELECT [column,]group_function(column)...
FROM table
[WHERE condition]
[GROUP BY [ROLLUP]group_by_expression]
[HAVING having_expression]
[ORDER BY column] ;
1)ROLLUP分组产生一个包含常规分组行和小计值的结果集
2)ROLLUP是一个GROUP BY 子句的扩展
3)用ROLLUP操作产生小计和累计
例:
SELECT deptno department_id,job job_id,SUM(sal) salary
FROM emp
WHERE deptno<60
GROUP BY ROLLUP(deptno,job);
结果:
DEPARTMENT_ID JOB_ID SALARY
10 3000
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 11750 //dept10薪水小计
20 CLERK 3100
20 ANALYST 6000
20 MANAGER 2975
20 12075 //dept20薪水小计
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 //dept30薪水小计
33225 //所有salary之和
4.CUBE操作
SELECT [column,]group_function(column)...
FROM table
[WHERE condition]
[GROUP BY [CUBE]group_by_expression]
[HAVING having_expression]
[ORDER BY column] ;
1)CUBE是GROUP BY子句的扩展
2)CUBE分组是产生一个包含ROLLUP行和交叉表行的结果集
例:
SELECT deptno department_id,job job_id,SUM(sal) salary
FROM emp
WHERE deptno<60
GROUP BY CUBE(deptno,job)
ORDER BY 1;
结果:
DEPARTMENT_ID JOB_ID SALARY
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 3000
10 11750
20 ANALYST 6000
20 CLERK 3100
20 MANAGER 2975
20 12075
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
ANALYST 6000
CLERK 5350
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
3000
33225
5.GROUPING函数
SELECT [column,]group_function(column),GROUPING(expr)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP][CUBE]group_by_expression]
[HAVING having_expression]
[ORDER BY column] ;
1)GROUPING函数既可以用于CUBE操作,也可以用于ROLLUP操作
2)用GROUPING函数模拟能够发现在一行中的构成小计的分组
3)GROUPING函数返回0或1
例:
SELECT deptno deptid,job,SUM(sal),
GROUPING(deptno) GRP_DEPT,
GROUPING(job) GRP_JOB
FROM emp
WHERE deptno<50
GROUP BY ROLLUP(deptno,job);
结果:
DEPTID JOB SUM(SAL) GRP_DEPT GRP_JOB
10 3000 0 0
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 11750 0 1
20 CLERK 3100 0 0
20 ANALYST 6000 0 0
20 MANAGER 2975 0 0
20 12075 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 5600 0 0
30 9400 0 1
33225 1 1
已选择14行。
6.分组集
1)GROUPING SETS是GROUP BY子句更进一步的扩展
2)能够用GROUPING SETS在同一查询中定义多个分组
3)Oracle服务器计算在GROUPING SETS子句中指定的所有分组
4)分组集合的效率:
-对基表仅进行一次查询
-不需要写复杂的UNION语句
-GROUPING SETS有更多的元素,更好的执行性能
例:
SELECT deptno department_id,job job_id,
mgr manager_id,AVG(sal) salary
FROM emp
GROUP BY GROUPING SETS((deptno,job),(job,mgr));
结果:
DEPARTMENT_ID JOB_ID MANAGER_ID SALARY
10 3000
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 CLERK 1550
20 ANALYST 3000
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
3000
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 2000
ANALYST 7566 3000
MANAGER 7839 2758.33333
SALESMAN 7698 1400
PRESIDENT 5000
已选择19行。
7.复合列
1)复合列是一个作为整体被处理的列集合
-ROLLUP(a,(b,c),d)
2)为了指定复合列,用GROUP BY子句来分组在圆括号内的列,因此Oracle服务器在进行ROLLUP
或CUBE操作时将它们作为一个整体来处理
3)当使用ROLLUP或CUBE时,复合列将跳过在确定级别上的聚合
例:
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno,(job,mgr));
结果:
DEPTNO JOB MGR SUM(SAL)
10 3000
10 CLERK 7782 1300
10 MANAGER 7839 2450
10 PRESIDENT 5000
10 11750
20 CLERK 7788 1100
20 CLERK 7902 2000
20 ANALYST 7566 6000
20 MANAGER 7839 2975
20 12075
30 CLERK 7698 950
30 MANAGER 7839 2850
30 SALESMAN 7698 5600
30 9400
33225
练习
1.查询emp表,根据部门编号、工作岗位、所属的经理编号进行分组,使用ROLLUP查询薪水的合计
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno,job,mgr);
2.查询emp表,根据部门编号、工作岗位、所属的经理编号进行分组,使用CUBE查询薪水的合计
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY CUBE(deptno,job,mgr);
3.用GROUPING SETS写一个查询,显示下面的分组:
deptno,mgr,job
deptno,job
mgr,job
查询计算每个分组的工资总数
SELECT deptno,job,mgr,SUM(sal)
FROM emp
GROUP BY GROUPING SETS((deptno,job,mgr),(deptno,job),(mgr,job));