子查询
MySQL筛选组中一些特殊值
问题1 查询平均工资最低的部门信息
解法1 多个子查询嵌套
1.查找各部门的平均工资
select avg(salary),dept_id
from enployees
group by dept_id
2.查询1结果上的平均工资最低的值
select min(ag)
from (select avg(salary) ag,dept_id
from enployees
group by dept_id) ag_dep;
3.查出工资最低的部门id
第2步中不能直接查出其id,因为在条件中已进行过分组。
select avg(salary),dept_id
from enployees
group by dept_id
having avf(salary) = (
select min(ag)
from (select avg(salary) ag,dept_id
from enployees
group by dept_id) ag_dep
);
4.查询部门信息
select d.*
from departments d
where d.dept_id=(
select dept_id
from enployees
group by dept_id
having avf(salary) = (
select min(ag)
from (select avg(salary) ag,dept_id
from enployees
group by dept_id) ag_dep ));
解法2 利用limit
1.查找各部门的平均工资
select avg(salary),dept_id
from enployees
group by dept_id
2.查询1结果上的平均工资最低的部门编号
select dept_id
from enployees
group by dept_id
order by avg(salary)
limit 0,1;
3.查询部门信息
select *
from departments
where det_id = (select dept_id
from enployees
group by dept_id
order by avg(salary)
limit 0,1);
问题2 查询平均工资最低的部门信息及其对应的平均工资
1.查找各部门的平均工资
select avg(salary),dept_id
from enployees
group by dept_id
2.查询1结果上的平均工资最低的部门编号
select dept_id
from enployees
group by dept_id
order by avg(salary)
limit 0,1;
3.查询部门信息及其对应工资 不能直接得出,需要利用连接查询
select d.*,
from departments d
join ( select avg(salary) ag,dept_id
from enployees
group by dept_id
order by avg(salary)
limit 0,1) ag_dep
on d.dept_id=ag_dep.dept_id
问题3 查询部门平均工资高于公司平均工资
1.查询公司平均工资
select avg(salary),dept_id
from enployees;
2.查询部门平均工资
select dept_id
from enployees
group by dept_id;
3.查询2中大于1的值
select avg(salary),dept_id
from enployees
group by dept_id
having avg(salary)>(select avg(salary),dept_id
from enployees)