聚集函数
亦称分组函数、聚合函数。聚集函数可以对行集进行操作,并且为每组给出一个结果。
聚集函数可以使用任何有效的表达式
NULL
值在聚集函数中将被忽略
可以在聚集函数中使用
DISTINCT
关键字,排除重复值
聚集函数分为以下几种
AVG(x)
:返回
x
的平均值
COUNT(x)
:返回统计的行数
MAX(x)
:返回
x
的最大值
MIN(x)
:返回
x
的最小值
SUM(x)
:返回
x
的总计值
以下是不常用的
MEDIAN(x)
:返回中间值
STDDEV(x)
:返回标准偏差
VARIANCE(x)
:返回
x
的方差
■avg()
AVG(x)
获取
x
的平均值
例如,统计
EMP
表员工的平均工资
SELECTAVG(sal) FROM EMP;
例如,可以对每位员工工资加
200
后,对表达式求平均值
SELECT AVG(sal +200) FROM EMP;
■count()
COUNT(x)
统计查询返回的行数
例如,统计
EMP
表的行数
SELECTCOUNT(*) FROM EMP;
在统计时避免使用
COUNT(*)
,否则会使用更长的统计时间返回统计结果
可以使用某列的列名或
ROWID
代替星号
(*)
例如,可以使用
ROWID
来统计
products
表的行数
SELECTCOUNT(empno)FROM EMP;
SELECTCOUNT(rowid)FROM EMP;
■max()、min()
MAX(x)、MIN(x)
分别统计
x
的最大值和最小值
例如,统计
EMP
表
sal
列的最大值和最小值
SELECT MAX(sal),MIN(sal) FROM EMP;
MAX()
和
MIN()
函数,也可以用来统计字符串和日期
如果是用来统计字符串,那么将会对字母进行排序,最大值在最后底部,最小值在前面第一个。例如,
Albert
会在
Zeb
前面
如果是日期的话,最大值会是最近的时间,最小值会是最早的时间
例如,分别统计字符串列和日期列的最大值和最小值
SELECT MAX(ename),MIN(ename) FROM EMP;
SELECT MAX(hiredate),MIN(hiredate) FROM EMP;
■sum()
SUM(x)
返回所有
x
中的值的总和
例如,统计
EMP
表所有工资的总和
SELECTSUM(sal)FROM EMP;
■分组
有时需要对表中的行进行分组,然后统计每组的信息
例如:统计
EMP
表中所在部门,可以使用
GROUP BY
进行分组
SELECTdeptno FROMEMP GROUP BY deptno;
可以使用
GROUPBY
对多个列进行分组
例如:对
EMP
表中职位和部门进行分组
SELECTdeptno,job FROMEMP GROUP BY deptno,job;
可以对分组后的行使用聚集函数,聚集函数会统计每组中的值,对于每组分别统计后返回一个值
例如
:
按照部门编号分组,求出每个部门的人数,平均工资
Selectdeptno,avg(sal),count(empno)from EMP
Groupby deptno;
例如:按照职位分组,求出每个职位的最高和最低工资
Selectjob ,max(sal),min(sal)from EMP group by job
order by job;
可以多分组统计的结果,使用聚集函数进行排序
注意事项
如果查询中使用了聚集函数,被查询的列未使用聚集函数处理,那么这些列必须出现在
GROUPBY
子句后
否则,会提示
ORA-00937
错误
,
例如
select deptno,avg(sal) from EMP;
不能使用聚集函数作为
WHERE
子句的筛选条件
否则,会提示
ORA-00934
错误
,
例如
select deptno from EMP where avg ( sal )>1000;
select deptno from EMP where avg ( sal )>1000;
使用HAVING过滤分组后的行
可以使用
HAVING
子句过滤分组后的行
语法:
SELECT...FROM …WHERE
GROUPBY ...
HAVING ...
ORDERBY ...;
注意
GROUP BY
使用时可以不使用
HAVING
,但是使用
HAVING
时必须有
GROUP BY
例如,查看
EMP
表中
,
部门的平均工资超过
900
的部门,首先要使用
GROUPBY
对部门进行分组
,
然后,在统计出每个部门的平均工资后再对其进行筛选
SELECTdeptno, AVG(sal) FROM EMP
GROUPBY deptno HAVING AVG(sal) > 900;
例:
/*如何显示所有员工中最高工资和最低工资*/
select * from emp;
select max(e.sal) as"最高工资", min(e.sal) as"最低工资",avg(e.sal),sum(e.sal),count(*) from emp e;
/*请显示工资最高的员工的名字,工作岗位*/
select em.ename, em.job, em.sal
from emp em
where em.sal = (select max(e.sal) from emp e);
select em.ename, em.job, max(em.sal)
from emp em
group by em.ename, em.job
having max(em.sal) = (select max(e.sal) from emp e);
/*请显示工资高于平均工资的员工信息*/
select e.* from emp e where e.sal>(select avg(em.sal) from emp em);
/*请显示工资高于所在部门平均工资的员工信息*/
select em.empno,
em.ename,
em.job,
em.mgr,
em.hiredate,
em.sal,
em.deptno,
emp1.avgsal
from emp em,
(select avg(e.sal) as avgsal, e.deptno as deptno
from emp e
group by e.deptno) emp1
where em.deptno = emp1.deptno
and em.sal > emp1.avgsal