首页 专题 文章 代码 归档
Mysql 分组查询
2020.02.27 20:30 2020.02.28 09:13

1. 分组查询

语法:

select 分组函数,列(要求出现在group by 后面) 
from 表 
[where ...] 
group by 分组的列表 
[order by ...] 
[limit ....]

1、查询每个工种的最大工资

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

2、每个位置上的部门个数

select count(*), location_id
from locations
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;

复杂的筛选条件

1、查询哪个部分的员工数>2

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

这里就只能使用having,判断个数大于2;

2、查询每个工种有奖金的员工的最高工资>12000的工种

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

3、查询领导编号>102的每个领导手下的最低工资>5000的领导编号,以及其最低工资;

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

按多个字段分组

1、查询每个部门的每个工种的员工的平均工资

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

添加排:

2。查询每个部门的每个工种的员工的平均工资,并按工资排序

select avg(salary) s, department_id, job_id
from employees
where department_id is not null
group by department_id, job_id, job_id
order by s desc

2. 总结

特点:

筛选条件两种:where、having

1、where用于分组前筛选,位于group by语句前面;

2、having用于分组后筛选,位于group by语句后面;

本节阅读完毕! (分享
二维码图片 扫描关注我们哟