Oracle_高级子查询

1.多列子查询

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

//单列子查询的方式

  select  employee_id,manager_id,department_id

from employees e1

where manager_id in (

select manager_id from employee where employee_id in(141,174);

)and department_id in (

select department_id from employee where employee_id in(141,174);

)

and employee_id not in (141,174);

//多列子查询:将两个单列子查询合并

select  employee_id,manager_id,department_id

from employees e1

where (manager_id,department_id) in (

select manager_id,department_idfrom employee where employee_id in(141,174);

)

and employee_id not in (141,174);

2.在from子列中使用子查询

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

select last_name,department_id ,salary

from employees e1

where salary >(

select avg(salary) 

from employees e2

where e1.department_id=e2.department_id

group by department_id

);

//使用from字句

select last_name,department_id ,salary,avg_sal

from employees e1,(select department_id ,avg(salary) as avg_sal from employees group by department_id ) e2

where e1.department_id=e2.department_id;

3.单列子查询

条件表达式 case

显示员工employee_id,last_name和location。其中，若员工department_id和location_id 为 1800的department_id相同，则显示Canada，否则USA

select employee_id,last_name,

(case department_idwhen(select department_id from departments where location_id=1800) then 'Canda' else 'USA'end ) location

from employees;

在order by中使用单列子查询

显示员工的employee_id,last_name,要求按照员工department_name 排序

select employee_id,last_name

from employees e1

order by (select department_name

from departments d

where e1.department_id =d.department_id);

4.相关子查询

定义：相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

5.EXISTS 操作符

定义：如果子查询中满足条件行，则返回true，不满足条件返回false

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

select employee_id,last_name,job_id,department_id

from empolyee_id e1

where exists ( //只需要告诉我能查到，不需要返回具体数据

select 'asddsa' from employees e2 where e1.employee_id=e2.manager_id

);

6.WITH子句

查询公司中工资比Able高的员工的信息

select employee_id,salary

from employees

where salary >(

select salary from employees where last_name='Abel'

);

//使用with子句实现

with Abel_salas (select salary from employees where last_name='Abel') //先查询able的工资，把Abel_sal理解为表

select employee_id,salary

from employees

where salary >(

select salary form Abel_sal

)