mysql中where后面不能跟聚合函数会报错,换成group by… having…就行了。
1、
where后跟聚合函数count:
mysql> select d.dname,count(*) from emp e join dept d on d.deptno=e.deptno where count(empno)>=5;
报错信息:聚合函数的非法使用
ERROR 1111 (HY000): Invalid use of group function
2、
换成group by… having…:
mysql> select d.dname,count(*) from emp e join dept d on d.deptno=e.deptno group by e.deptno having count(empno)>=5;
最终正确显示:
±---------±---------+
| dname | count(*) |
±---------±---------+
| RESEARCH | 5 |
| SALES | 6 |
±---------±---------+
2 rows in set (0.00 sec)