MySQL基础学习（四）——分组查询group by

分组查询：GROUP BY函数

引入：查询每个部门的平均工资

select avg(salary) from employees;

一、分组数据：group by 子句语法

select 分组函数，列 （要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意：
where一定要放在from后面
查询列表比较特殊，要求是分组函数和groupby后出现的字段

二、简单分组

案例1：查询每个工种的最高工资

select max(salary) ,job_id
from employees
group by job_id;

案例2：查询每个位置上的部门个数

select count(*), location_id
from departments
group by location_id;

三、增加了分组前的筛选条件

案例3：查询邮箱中包含a字符的，每个部门的平均工资

select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;

案例4：查询有奖金的每个领导手下员工的最高工资（增加了分组前的筛选条件）

select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;

四、添加复杂的筛选条件

案例5：查询哪个部门的员工个数大于2
（1）查询每个部门员工的个数

select count(*),department_id
from employees
group by department_id;

（2）根据以上结果筛选，查询哪个部门的员工个数大于2

分组后的筛选用having而不是where

select count(*),department_id
from employees
group by department_id
having count(*)>2;

案例6：查询每个工种有奖金的员工的最高工资 >12000的工种编号和最高工资
（1）查询每个工种有奖金的员工的最高工资(分组前的筛选用where)

select max(salary), job_id
from employees
where commission_pct is not null
group by job_id;

（2）根据以上结果继续筛选max>12000

select max(salary), job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

案例7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资

select min(salary), manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;

总结一：
分组查询中的筛选条件分为两类
分组前筛选：数据源为原始表，位置在groupby子句的前面，关键字为where
分组后筛选：数据源为分组后的结果集，位置在groupby子句的后面，关键字为having
注：分组函数做条件筛选肯定是放在having子句中；能用分组前筛选的要优先使用分组前的筛选（性能问题）

五、按表达式进行分组或者函数

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些

select count(*),length(last_name) len1
from employees
group by len1
having count(*)>5;

注：mysql支持groupby和having后用别名，其他的DBMS不一定支持

六、 按多个字段进行分组

案例：每个部门每个工种的员工的平均工资

select avg(salary),job_id,department_id
from employees
group by job_id,department_id;

• 分组查询添加排序
• 案例：每个部门每个工种的员工的平均工资，并按平均工资的高低显示

select avg(salary),job_id,department_id
from employees
group by job_id,department_id
order by avg(salary) desc;

select avg(salary),job_id,department_id
from employees
where department_id is not null
group by job_id,department_id
having avg(salary)>10000
order by avg(salary) desc;

- 总结二
group by子句支持单个字段分组，多个字段分组（逗号隔开，无顺序要求），表达式或者函数（用的少）
支持添加排序，排序放在整个分组查询的最后

七、例题

1、查询各job_id的员工工资的最大值、最小值、平均值、总和，并按job_id升序

select max(salary),min(salary),avg(salary),sum(salary),job_id
from employees
group by job_id
order by job_id;

2、查询员工最高工资和最低工资的差距（difference）

select max(salary)-min(salary) difference
from employees;

3、查询各个管理者手下员工的最低工资，其中最低 工资不能低于6000，没有管理者的员工不计算在内

select min(salary),manager_id
from employees
where manager_id is not null
group by manager_id 
having min(salary)>=6000;

4、查询所有部门的编号，员工数量和工资平均值，并按平均工资降序

select count(*),avg(salary),department_id
from employees
group by department_id
order by avg(salary) desc;

5、选择具有各个job_id的员工人数

select  count(*) 个数,job_id
from employees
where job_id is not null
group by job_id;