Oracle学习笔记 6 -- 分组函数

上一次笔记学习了，多表查询，本次笔记来学习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)
------------
         14

count()函数括号中，可以使用*，*表示返回所有记录的总数。可以使用列名，列名表示返回该列记录中不为空的记录总数。在上面的结果中，查询部门号，在结果中显示有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: 不是单组分组函数提示。