使用SCOTT用户登录
分组查询
求员工工资的平均值和工资总额
SELECT AVG(SAL),SUM(SAL) FROM EMP;
求员工工资的最大值和最小值
SELECT MAX(SAL),MIN(SAL) FROM EMP;
求员工总数人数
SELECT COUNT(EMPNO) FROM EMP; SELECT COUNT(*) FROM EMP;
求出部门数(使用DISTINCT 关键字去掉重复的部门号)
SELECT COUNT(DISTINCT DEPTNO) FROM EMP;
行转列 WM_CONCAT
SET LINESIZE 200
COL 部门中员工的姓名 FOR A60
SELECT DEPTNO 部门号,WM_CONCAT(ENAME) 部门中员工的姓名 FROM EMP GROUP BY DEPTNO;
部门号 部门中员工的姓名
---------- ------------------------------------------------------------
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
分组函数与空值
分组函数会自带忽略空值,可以使用NVL函数使分组函数无法忽略空值。
SELECT COUNT(*),COUNT(COMM) FROM EMP;--COMM字段有空值,所以结果不一样。
COUNT(*) COUNT(COMM)
---------- -----------
14 4
SELECT COUNT(*),COUNT(NVL(COMM,0)) FROM EMP;--NVL第一个参数为空返回第二个参数的值,否则返回第一个
COUNT(*) COUNT(NVL(COMM,0))
---------- ------------------
14 14
分组数据 GROUP BY 子句
求每个部门的平均工资,要求结果显示部门号,部门的平均工资
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;--在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY 子句中,包含在组函数中的列不必包含在SELECT列表中。
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
求每个部门的平均工资,要求结果显示部门的平均工资
SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO;
AVG(SAL)
----------
1566.66667
2175
2916.66667
使用多个列分组,按部门,不同的职位,统计员工的工资总额
SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;
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
过滤分组 HAVING子句 (不能在WHERE子句中使用组函数,在HAVING子句中可以)
求平均工资大于2000的部门
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;
DEPTNO AVG(SAL)
---------- ----------
20 2175
10 2916.66667
查询10号部门的平均工资
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO=10;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
SELECT DEPTNO,AVG(SAL) FROM EMP WHERE DEPTNO =10 GROUP BY DEPTNO;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
--从SQL优化的角度,尽量使用WHERE;HAVING先分组再过滤,WHERE先过滤,再分组。WHERE使得分组记录数大大降低,从而提高效率。
在分组查询中使用ORDER BY子句
求每个部门的平均工资,要求显示部门号,部门的平均工资,并且按照工资升序排列
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL);
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
分组函数嵌套
求部门平均工资的最大值
SELECT MAX(AVG(SAL)) FROM EMP GROUP BY DEPTNO;
MAX(AVG(SAL))
-------------
2916.66667
GROUP BY 增强(适合报表)
SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB);
BREAK ON DEPTNO SKIP 2
SET PAGESIZE 30
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750
20 CLERK 1900
ANALYST 6000
MANAGER 2975
10875
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400
29025
--GROUP BY ROLLUP(DEPTNO,JOB)=GROUP BY DEPTNO,JOB+GROUP BY DEPTNO+GROUP BY NULL(不按照任何条件分组)