Author: 刘老师(Aaron Lau) 武汉长乐教育，武汉PHP培训课程，版权所有，转载请注明！

Mysql中的group by用法解析

group by即分组查询,请各位同学务必掌握好,面试必备!

下面看案例

1. 列出每个部门最高薪水的结果

SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT

2. 查询每个部门的总的薪水数

SELECT DEPT, sum( SALARY ) AS total
FROM STAFF
GROUP BY DEPT

3. WHERE与GROUP BY一起使用

SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM
FROM staff
WHERE HIREDATE > '2010-01-01'
GROUP BY DEPT, EDLEVEL
ORDER BY DEPT, EDLEVEL

4. 在GROUP BY之后使用HAVING

寻找雇员数超过2个的部门的最高和最低薪水：

SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING COUNT(*) >2
ORDER BY DEPT

寻找雇员平均工资大于3000的部门的最高和最低薪水：

SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM
FROM staff
GROUP BY DEPT
HAVING AVG( SALARY ) >3000
ORDER BY DEPT

5. having与where的区别

注意: having 和where 都是用来筛选的 having 是筛选组 而where是筛选记录

1. 当分组筛选的时候 用having
2. 其它情况用where

用having就一定要和group by连用， 用group by不一有having（它只是一个筛选条件用的）