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/