mysql必知必会——GROUP BY和HAVING
置顶2016年09月16日 23:58:49
阅读数：32217
GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
select子句中的列名必须为分组列或列函数，列函数对于group by子句定义的每个组返回一个结果。
某个员工信息表结构和数据如下：
[sql] view plain copy
select dept , max(salary) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
查询结果如下：
[sql] view plain copy
SELECT dept,edlevel,MAX(salary) AS MAXIMUM
FROM STAFF
WHERE hiredate > '2010-01-01'
GROUP BY dept,edlevel
查询结果如下：
[sql] view plain copy
SELECT dept ,MAX(salary) AS MAXIMUM ,MIN(salary) AS MINIMUM
FROM STAFF
GROUP BY dept
HAVING COUNT(*) > 2
ORDER BY dept
查询结果如下：
[sql] view plain copy
SELECT dept,MAX(salary) AS MAXIMUM,MIN(salary) AS MINIMUM
FROM STAFF
GROUP BY dept
HAVING AVG(salary) > 3000
ORDER BY dept
查询结果如下：
