MySQL聚合统计
【数据库】MySQL聚合统计
王笃笃-CSDN博客
https://blog.csdn.net/wangduduniubi?type=blog显示平均工资低于2000的部门和它的平均工资
mysql> select deptno,avg(sal) deptavg from emp group by deptno;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.01 sec)
mysql> select deptno,avg(sal) deptavg from emp group by deptno having deptavg<2000;
+--------+-------------+
| deptno | deptavg |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
having 对聚合后的数据进行条件筛选。
having 和 where 的区别
条件筛选的阶段不同
mysql> select deptno,job,avg(sal) myavg from emp where ename != 'SMITH' group by deptno,job having myavg<2000;
+--------+----------+-------------+
| deptno | job | myavg |
+--------+----------+-------------+
| 30 | SALESMAN | 1400.000000 |
| 20 | CLERK | 1100.000000 |
| 30 | CLERK | 950.000000 |
| 10 | CLERK | 1300.000000 |
+--------+----------+-------------+
where
对具体的任意列进行条件筛选
having
对分组聚合之后的结果进行条件筛选。
条件筛选的阶段不同
1
from emp
2
where ename != 'SMITH'
3
group by deptno,job
4
deptno,job,avg(sal) myavg
5
having myavg<2000;
中间筛选出来的和最终结果——>全部可以看做是逻辑上的表-->mysql 中一切皆表