SQL子查询1

子查询

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)