Oracle分组函数小结,Oracle之组函数及分组统计（重点）

5.1组函数及分组统计(重点)

分组：例如 把所有男生分为一组，女生分为一组。必须要有分组条件

如果想求出每一组的平均身高，平均年龄，这就需要使用分组函数

5.1.1组函数

在SQL中常使用的组函数有以下几个：

1、COUNT(): 求出全部的记录数

2、MAX():求出一组中最大的值

3、MIN():求出最小值

4、AVG():求出平均值

5、SUM():求和

范例：

SELECT COUNT(empno) FROM emp;

SELECT MIN(sal) FROM emp;

SELECT MAX(sal) FROM emp;

SELECT SUM(sal) FROM emp WHERE deptno=20;

SELECT AVG(sal) FROM emp;

5.1.2分组统计

要使用分组统计，则首先应该固定其语法，使用GROUP BY 进行分组，此时的SQL语句格式如下：

SELECT(DISTINCT)*|查询列1 别名1,查询列2 别名2...

FROM 表名称1 别名1,表名称2 别名2

(WHERE 条件)

(GROUP BY 分组条件)

(ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC)

范例：求出每个部门的雇员数量，肯定是应该按照部门编号进行分组

SELECT deptno ,COUNT(empno)

FROM emp

GROUP BY deptno;

范例：求出每个部门的平均工资

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno;

范例：SELECT deptno,COUNT(empno) FROM emp;

以上代码不能正确执行，是因为：

1、如果程序中使用了分组函数，则有两种可以使用的情况

程序中存在FROUP BY，并制定了分组条件，这样可以将分组条件一起查询起来

如果不是用分组的话，则只能单独的使用分组函数

2、在使用分组函数的时候，不能出现分组函数和分组条件之外的字段；

范例: SELECT deptno,empno,COUNT(empno)

FROM emp

GROUP BY deptno;

会提示empno不是分组字段，所以无法使用

范例：按部门分组，并显示部门的名称及每个部门的员工数

SELECT d.dname,COUNT(e.empno)

FROM emp e,dept d

WHERE e.deptno=d.deptno

GROUP BY d.dname;

范例：要求显示出平均工资大于2000的部门编号和平均工资

SELECT deptno,AVG(sal)

FROM emp

WHERE AVG(sal)>2000

GROUP BY deptno;

查询结果显示错误，因为分组函数只能在分组中使用，不允许在WHERE子句中出现，

那么如果现在假设要指定分组的条件，则只能通过第二种条件的指令：HAVING，

此时的SQL语法如下：

SELECT(DISTINCT)*|查询列1 别名1,查询列2 别名2...

FROM 表名称1 别名1,表名称2 别名2

(WHERE 条件)

(GROUP BY 分组条件)(HAVING 分组条件)

(ORDER BY 排序字段 ASC|DESC,排序字段 ASC|DESC)

完善以上SQL语句为：

SELECT deptno,AVG(sal)

FROM emp

GROUP BY deptno HAVING AVG(sal)>2000;

尝试：显示非销售人员工作名称以及从事统一工作雇员的月工资的总和，并且要满足从事同一工作的

雇员的月工资合计大于5000，输出结果按月工资的合计升序排列

分析 ：1、显示全部的非销售人员      SELECT * FROM emp WHERE job <> 'SALESMAN';         2、显示同一工作雇员的月工资的总和(显然是按工作进行分组)      SELECT job,SUM(sal)      FROM emp      WHERE job !='SALESMAN'      GROUP BY job;        3、满足从事统一工作的的雇员月工资合计大于5000      SELECT job,SUM(sal)      FROM emp      WHERE job !='SALESMAN'      GROUP BY job HAVING SUM(sal)>5000;        4、按照月工资合计升序排列      SELECT job,SUM(sal)su      FROM emp      WHERE job !='SALESMAN'      GROUP BY job HAVING SUM(sal)>5000      ORDER BY su;  总结：只要一列上存在重复的内容才有可能考虑到分组  注意：分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句；   范例：求出平均工资 最高的部门工资；   错误代码如下：   SELECT deptno ,MAX(AVG(sal))   FROM emp   GROUP BY deptno;   错误提示为：不是单组分组函数 此时可以去掉 deptno字段   或则可以改成      SELECT MAX(deptno) ,MAX(AVG(sal))   FROM emp   GROUP BY deptno;