14、SQL--高级子查询（1）

 

 

 

 

（1）、查询last_name为Chen的manager的信息：（子查询）

select employee_id,last_name

from employees

where employee_id=(

                   select manager_id

                   from employees

                   where last_name='Chen'

                   ) 

 

 

一、多列子查询：

 

 

 

代码实现：

（1）、解法一：（未使用多列子查询）

select employee_id,manager_id,department_id

from employees

where manager_id in

                    (

                     select manager_id

                     from employees

                     where employee_id in (141,174)

                    )

and department_id in(

                     select department_id

                     from employees

                     where employee_id in (141,174)

                    )               

and employee_id not in (141,174)

附：每次都返回一个查询的结果。

 

（2）、解法二：（使用多列子查询）成对- -点对

select employee_id,manager_id,department_id

from employees

where (manager_id,department_id) in

                                 (

                                 select manager_id,department_id

                                 from employees

                                 where employee_id in (141,174)

                                 )

and employee_id not in (141,174)；  --其他员工

附：返回多个查询的结果，与外层的条件对应；。

 

 

 

 

 

二、在FROM子句中使用子查询：

 

 

（1）、方法一：不用FROM内的子查询（存在大量冗余- -重复代码）

select last_name,department_id,salary,

(select avg(salary) from employees e3 where e1.department_id = e3.department_id group by department_id)

from employees e1

where salary > (

                select avg(salary)

                from employees e2

                where e1.department_id = e2.department_id

                group by department_id

               )

 

（2）、使用FROM内的子查询：

select last_name,e1.department_id,salary,e2.avg_sal



from employees e1,(select department_id,avg(salary) avg_sal

                   from employees

                   group by department_id) e2

where e1.department_id = e2.department_id

附：from后面的表是使用select语句生成的。

 

 

三、单列子查询表达式：

 

 

1、单列子查询：

 

（1）、单列子查询的实现：

select employee_id,last_name,

(case department_id when (select department_id from departments where location_id = 1800) then 'Canada'

                    else 'USA' end) location  --location为别名

from employees

 

2、在ORDER  BY中使用单列子查询：

 

 

（1）、在ORDER  BY中使用单列子查询：

select employee_id,last_name

from employees e

order by (

          select department_name

          from departments d

          where e.department_id = d.department_id

         )