mysql select/count()/group/case使用总结

一Gourp by和count(*)搭配

#案例1：查询每个位置的部门个数

先根据location_id进行分组,再统计该组的个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
 

#案例2：查询每个工种的员工平均工资
根据job_id进行分组,接着统计以salary为依据的平均工资

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

二count/case搭配分组统计

select count(case when score between 80 and 100 then 1 end)as "80到100",
count(case when score between 60 and 80 then 1 end)as "60到80",
count(case when score between 40 and 60 then 1 end)as "40到60",
count(case when score between 20 and 40 then 1 end)as "20到40",
count(case when score between 0 and 20 then 1 end)as "0到20" from stu_exam;