上一次笔记学习了,多表查询,本次笔记来学习Oracle中的分组函数。
首先来了解一下什么是组函数:
组函数:作用于一组数据,并对一组数据返回一个值。
SQl中组函数的类型:
COUNT():求出全部的记录数。
MAX():求出一组数据中的最大值。
MIN():求出最小值。
AVG():求平均值。
SUM():求和。
数值型数据可以使用AVG()和SUM()函数;MIN()和MAX()函数适用于任意数据类型,但是一般应用在数值类型上。
基本语法:
SELECT [column], group_function(column), ...
FROM table
[WHERE condition]
例:使用count()函数,求出雇员表中记录总数。
SQL> select count(*) from emp ;
COUNT(*)
----------
14
SQL> select count(deptno ) from emp ;
COUNT(EMPNO)
------------
14count()函数括号中,可以使用*,*表示返回所有记录的总数。可以使用列名,列名表示返回该列记录中不为空的记录总数。在上面的结果中,查询部门号,在结果中显示有14条数据,在部门表中只有4条数据,那么返回的查询结果中存在重复的情况。可以使用distinct 关键字,来去重。即:select count(distinct deptno ) from emp ; , 返回的结果为3。
例:使用MAX 、MIN,求雇员表中,员工的最高工资和最低工资。
SQL> select max(sal) , min(sal ) from emp ;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
例:使用AVG、SUM函数,求雇员表中,30号部门的工资平均值和工资总和。
SQL> select sum(sal) ,avg(sal) from emp where deptno = 30 ;
SUM(SAL) AVG(SAL)
---------- ----------
9400 1566.66666
使用group by 字句,来为数据分组。
基本语法:
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column asc | desc];
例:求雇员表中每一个部门的雇员数量
SQL> select deptno , count(deptno)
2 from emp
3 group by deptno ;
DEPTNO COUNT(DEPTNO)
------ -------------
30 6
20 5
10 3按照deptno对数据进行分组,之后统计每一个分组中的记录总数。并将结果返回。
例:求每个部门的平均工资,结果取整。
SQL> select round(avg(sal)) , deptno from emp group by deptno ;
ROUND(AVG(SAL)) DEPTNO
--------------- ------
1567 30
2175 20
2917 10
注意:
跟在select后面的字句,除了函数外,也可以跟列名。如果跟列名那么应该和group by后面的列名一致。否则出现ORA-00979: 不是 GROUP BY 表达式错误。
例:
select deptno ,empno ,count(empno)
from emp
group by deptno
ORA-00979: 不是 GROUP BY 表达式 ,empno 为多余的列。
而
select deptno , count(empno)
from emp
group by deptno ; 正确执行。
例:求平均工资大于200 的部门
select deptno
from emp
where avg(sal) > 2000
group by deptno
ORA-00934: 此处不允许使用分组函数当我们执行,上面的sql语句的时候提示,where后面不允许使用分组函数。即:分组函数不允许在where语句之后出现。如果要对分组函数加入限制条件,使用HAVING 子句。HAVING子句必须与GROUP BY 子句连用,可以放在GROUP BY之前,也可以房子之后。GROUP BY可以单独使用。
即:
select deptno
from emp
having avg(sal) > 2000
group by deptno结果为:
DEPTNO
------
20
10
例:查询部门平均工作中最大的工资
SQL> select max(avg(sal)) from emp group by deptno ;
MAX(AVG(SAL))
-------------
2916.66666666
通过上面的结果,说明组函数可以嵌套使用。但是select 后面不能再出现 deptno字段名,否则出现ORA-00937: 不是单组分组函数提示。