mysql 分组查询

-- SELECT stu_gender,GROUP_CONCAT('stu_name') FROM student GROUP BY stu_gender
-- SELECT stu_gender AS gender,GROUP_CONCAT('stu_name') FROM student GROUP BY stu_gender ;

-- SELECT stu_gender AS gender,stu_age,COUNT('stu_name') from student GROUP BY stu_gender,stu_age;

-- 分组加聚合运算
-- SELECT stu_gender,GROUP_CONCAT(stu_age),max(stu_age) FROM student GROUP BY stu_gender;
-- SELECT stu_gender,stu_age,GROUP_CONCAT(stu_name) FROM student GROUP BY stu_gender,stu_age;

 查询每个部门的名称以及每个部门的人数
 SELECT department,GROUP_CONCAT(name),COUNT('id') FROM employee GROUP BY department;
 查询每个部门的部门名称以及每个部门工资大于1500的人数
 SELECT department name,salary FROM employee WHERE salary > 1500 GROUP BY department;


总结 分组后接聚合函数，是对每个组进行运算，不是对全部的进行。where 是在聚合前进行筛选
having 是在聚合之后进行筛选   ！！注意 ，where 后面不能加聚合函数

GROUP BY + having  having和where 作用相同，但是having只能加到group by后面

实例：1查询工资总和大于9000部门的名称
 SELECT separtment,GROUP_CONCAT(salary),sum(salary) FROM employee
 GROUP BY department HAVING SUM(salary)>9000;

2查询工资大于2000的，工资总和大于6000的部门名称以及工资和,然后降序排列

SELECT department,GROUP_CONCAT(salary),sum(salary)
 from employee where salary>2000
 GROUP BY department
 HAVING sum(salary)>6000,ORDER BY sum(salary) DESC;