第五章 分组查询

分组查询

关键字

GROUP BY

语法规则

例子

按照职位分组，查询每个职位的平均工资、最高工资、最低工资和人数

SELECT job,AVG(sal),MAX(sal),MIN(sal),COUNT(empno) FROM emp GROUP BY job;

练习

1. 统计所有部门的平均工资和最高工资。

SELECT dname,AVG(sal),MAX(sal) 
FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno GROUP BY e.deptno;

2. 统计10部门和20部门中各个职位的人数。

SELECT job,COUNT(empno) FROM emp WHERE deptno IN(10,20) GROUP BY job;

3. 统计公司所有部门的平均工资，按照平均工资降序排序。

SELECT dname,AVG(sal) 
FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno 
GROUP BY e.deptno 
ORDER BY AVG(sal) DESC;

注

统计函数与分组查询密切相关

多表+分组

例子

查询出每个部门的编号、部门名称、位置、部门人数、平均工资

SELECT d.deptno,dname,loc,COUNT(ename),AVG(sal) 
FROM dept d LEFT 
JOIN emp e ON d.deptno=e.deptno 
GROUP BY dname;

HAVING子句

引例

统计每个职位的平均工资，显示平均工资高于2000的职位

SELECT job,AVG(sal)
FROM emp
WHERE AVG(sal)>2000
GROUP BY job;

该语句报错

原因

WHERE中不允许出现任何统计函数
WHERE是在GROUP BY之前执行

应引入HAVING子句

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

作用

分组后进行筛选

语法格式

WHERE与HAVING的区别

WHERE是在分组之前对个体进行筛选 HAVING是在分组后对组别进行筛选
WHERE可以独立存在 HAVING依赖于GROUP BY
通常WHERE不适用于统计函数 HAVING常使用统计函数

例子

1

SELECT dname,COUNT(empno),MAX(sal),MIN(sal) 
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
GROUP BY dname HAVING MIN(sal)>1000;

2

SELECT grade,COUNT(ename),AVG(sal+IFNULL(comm,0)) 
FROM salgrade s LEFT JOIN emp e ON sal BETWEEN losal AND hisal
GROUP BY grade HAVING AVG(sal+IFNULL(comm,0))>1500 
ORDER BY AVG(sal+IFNULL(comm,0)) DESC;

3

SELECT COUNT(empno),AVG(sal) FROM emp WHERE comm IS NULL
UNION ALL
SELECT COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL;