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;