12、组函数、分组统计

4.7、组函数及分组统计（重点）

       分组：例如，把所有的男生分为一组，女生分为一组。

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

4.7.1、组函数

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

              ·COUNT()：求出全部的记录数

              ·MAX()：求出一组中的最大值

              ·MIN()：求出最小值

              ·AVG()：求出平均值

              ·SUM()：求和

范例：COUNT()函数

SELECT COUNT(empno) FROM emp;

范例：MAX()、MIN()，求最大最小值，一般是针对于数字的应用上。

       ·求出所有员工的最低工资

SELECT MIN(sal) FROM emp;

       ·求出所有员工的最高工资

SELECT MAX(sal) FROM emp;

范例：AVG()、SUM()，求和及平均值

       ·求出20部门中的总工资

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

       ·求出所有员工的平均工资

SELECT AVG(sal) FROM emp;

4.7.2、分组统计

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

SELECT {DISTINCT}*｜ 查询列1 别名1，查询列2 别名2，… FROM 表名称1别名1，表名称2 别名2，… {WHERE 条件(s)} {GROUP BY 分组条件} {ORDER BY 排序的字段 ASC|DESC，排序的字段 ASC|DESC，…}

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

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、  如果程序中使用了分组函数，则有两种可以使用的情况：

·程序中存在了GROUP BY，并指定了分组条件，这样可以将分组条件一起查询出来。

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

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

SELECT deptno,empno,COUNT(empno) FROM emp GROUP BY deptno;

       此时，提示empno不是GROUP BY表达式，所以无法使用。

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

SELECT d.dname,COUNT(e.empno) FROM dept d,emp e WHERE d.deptno=e.deptno GROUP BY d.dname;

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

       ·条件：AVG(sal)>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 条件(s)} {GROUP BY 分组条件 {HAVING 分组条件}} {ORDER BY 排序的字段 ASC|DESC，排序的字段 ASC|DESC，…}

范例：使用HAVING完成以上的操作

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

范例：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列：

       1、显示全部的非销售人员：job<>’SALESMAN’

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;

       ·正确的代码：

SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21817401/viewspace-621585/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21817401/viewspace-621585/