oracle学习日志03

最新推荐文章于 2023-04-11 18:21:20 发布

原创最新推荐文章于 2023-04-11 18:21:20 发布 · 438 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文详细介绍了SQL中的聚集函数，包括AVG、COUNT、MAX、MIN和SUM等常用函数的使用方法，以及MEDIAN、STDDEV和VARIANCE等不常用函数的应用场景。此外，还讲解了如何使用GROUP BY进行数据分组，以及如何结合HAVING子句进行分组后的数据筛选。

聚集函数

亦称分组函数、聚合函数。聚集函数可以对行集进行操作，并且为每组给出一个结果。

聚集函数可以使用任何有效的表达式

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;

使用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