子查询注意点

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 ;

 

posted @ 2022-03-23 11:09  jue1e0  阅读(57)  评论(0)    收藏  举报