子查询注意点
1.对以下例子进行分析:
#查询平均工资最低的部门信息和该部门的平均工资(相关子查询)
select d.*,(select avg(salary) from employees e2 where d.department_id = e2.department_id ) "avg_sal" from departments d where department_id in ( select department_id from employees e group by department_id having avg(salary) <= all( select avg(salary) #MIN(AVG(salary))是非法语句,所以不能用=min 的写法 from employees e2 group by department_id ) );
因为此处要查该部分的平均工资,这个量不能通过单表直接得到,而要由表的连接得到,所以再申明这个量时:
使用select语句选出这个量,然后from (salary所在的表),然后再建立(部份表和员工表)的连接,此处无需用group by语句对不同部门进行分组。
2.子查询的几种常用方法:
#查询平均工资最低的部门信息
(1).
1 select * 2 from departments d 3 where department_id in ( 4 select department_id 5 from employees e 6 group by department_id 7 having avg(salary) <= all( 8 select avg(salary) 9 from employees e2 10 group by department_id 11 ) 12 );
(2).
1 select * 2 from departments d 3 where department_id in ( 4 select department_id 5 from employees e 6 group by department_id 7 having avg(salary) = ( 8 select min(avg_salary) 9 from ( 10 select avg(salary) "avg_salary" 11 from employees 12 group by department_id 13 ) avg_sal 14 ) 15 );
注意此处在创建子表的过程中,一定要给子表命名为(avg_sal),且此处命名不要加单引号或者双引号
(3).
1 select * 2 from departments d 3 where department_id in ( 4 select department_id 5 from employees e 6 group by department_id 7 having avg(salary) = ( 8 select avg(salary) 9 from employees e2 10 group by department_id 11 order by avg(salary) asc 12 limit 0,1 13 ) 14 );
是limit 0,1而不是limit(0,1)
以上三种方法都完全只用到了子查询的思想
(4).子查询和多表查询的联合使用
1 select d2.* 2 from departments d2 ,( 3 select department_id ,avg(salary) 4 from employees e3 5 group by department_id 6 order by avg(salary) asc 7 limit 0,1 8 ) dept 9 where d2.department_id = dept.department_id ;