Day13 高级子查询

1. 多列子查询

 

示例：

查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id和department_id

 

select employee_id,manager_id,department_id

from employees

where  employee_id not in (141,174) and (manager_id,department_id) in (

                                                                        select manager_id,department_id

                                                                        from employees

                                                                        where employee_id in (141,174)

                                                                        )

 

多列子查询，就是在子查询只返回一列的基础上返回多列的结果。

 

2. from语句子查询

 

示例:

查询比本部门平均工资高的员工的last_name,department_id,salary及平均工资

 

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

 

3. 单列子查询

 

子查询的位置可以是任意的。

 

示例：

查询员工的employee_id,last_name，要求按照员工的department_name来排序

 

select employee_id,last_name

from employees e

order by (

          select department_name

          from departments d

          where e.department_id = d.department_id

          )

 

4. 相关子查询

 

5. Exists操作符

 

 

 

 

示例：

 

查询公司管理者的employee_id,last_name,job_id,department_id等信息

 

select employee_id,last_name,job_id,department_id

from employees e1

where exists (

              select 'X'

              from employees e2

              where e1.employee_id = e2.manager_id

                )

 

事实上，这里不需要返回具体的manager_id等信息，只需要确保e1.employee_id = e2.manager_id这个条件成立即可。

 

6. 相关更新

 

 

 

7. with子句

查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息

 

with dept_sumsal as (select department_name, sum(salary) sum_sal1

from departments d,employees e

where d.department_id = e.department_id

group by department_name

),

dept_avgsal as(

select sum(sum_sal1)/count(*) avg_sal

from dept_sumsal

)

 

select *

from dept_sumsal

where sum_sal1 > (

                  select avg_sal

                  from dept_avgsal

                  )

order by department_name