分组查询
分组函数:作用于一组数据,并对一组数据返回一个值
常用的分组函数:AVG SUM MIN MAX COUNT VM_CONCAT
最后一个是:行转列
SELECT AVG(sal),SUM(sal) FROM emp;
SELECT COUNT(*) FROM emp;
如果要求部门的个数
SELECT COUNT(distinct deptno) FROM emp;
distinct函数是去重
VM_CONCAT:行转列
示例:SELECT deptno,wm_concat(ename) FROM emp GROUP BY deptno;
示例:SELECT deptno 部门号,wm_concat(ename) 部门中员工的姓名 FROM emp GROUP BY deptno;
set linesize 200
col 部门中员工的姓名 for a60
SELECT deptno 部门号,wm_concat(ename) 部门中员工的姓名 FROM emp GROUP BY deptno;
员工表中有3个部门,部门当中有多个员工
wm_concat()可以用 , 分割
分值函数与空值
存在空值算平均奖金会出错
分组函数会自动忽略空值
可以使用NVL函数使分组函数无法忽略空值
NVL函数使ORACLE当中的允控函数,可以把空值转变成非空值
count(nvl(comm,0))
nvl函数就是当第一个参数为空则返回第二个参数,不为空就返回第一个参数
分组数据 GROUP BY
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
我们把这套select语句抽象成
SELECT a,组函数(x)
FROM table
GROUP BY a;
deptno不能再AVG(sal)的组函数里面, a不能再x里面
如果 查的是a,b,c 其中a、b、c没有在组函数里面 ,在组函数只是x
group by 子句必须a,b,c 否则会语法错误
在SELECT列表中所有未包含在组函数中的e列都应该包含在GROUP BY子句中
包含在 GROUP BY子句中的列不必包含在SELECT列表中
例如SELECT AVG(sal) FROM emp GROUP BY deptno;
使用多个列分组
SELECT a,组函数(x)
FROM table
GROUP BY a;
SELECT a,组函数(x)
FROM table
GROUP BY a;
多个列分组:按照部门不同职位统计员工工资的总和
就需要按照部门的部门号和邀功的职位两个列进行分组
SELECT deptno,job,sum(sal)
FROM emp
GROUP BY deptno,job;
SELECT deptno,job,sum(sal)
FROM emp
GROUP BY deptno,job ORDER BY deptno;
非法使用组函数
所有包含SELECT列表中,而未包含于组函数中的列都必须包含于GROUP BY子句中
过滤分组 使用having子句
分组函数也叫组函数也叫多行函数
SELECT deptno,avg(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;
使用HAVING过滤分组的数据
注意: 不能再WHERE子句中使用组函数
可以再HAVING子句中使用组函数
WHERE和HAVING都是过滤结果
例子:查询10号部门的平均工资
这个问题即可以使用WHERE也可以使用HAVING
从SQL优化的角度上看,尽量使用WHERE
HAING 分组的基础之上过滤分组的结果 也就是说先分组再过滤
WHERE 是先过滤再分组
比如员工用1亿个员工,10号部门只有100个 如果使用HAVING先分组就是1亿条
使用WHERE只会分组100个
WHERE可以大量提高效率
不过WHERE子句中不能使用分组函数
在分组查询中使用OREDER BY子句