Oracle子查询之高级子查询
Oracle 高级子查询
高级子查询相对于简单子查询来说，返回的数据行不再是一列，而是多列数据。
1，多列子查询
主查询与子查询返回的多个列进行比较
查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
select employee_id, manager_id, department_id
from employees
where (manager_id,department_id) in ( --由于内查询返回两列，所以此处也必须使用两列来进行对应,并且列的顺序一致
select manager_id,department_id --内查询返回2列
from employees
where employee_id in (141,174)
)
and employee_id not in (141,174);
2，FROM子句中使用子查询
将子查询的结果集，作为一个虚表，然后从这个虚表中查询数据行
返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
select last_name, e.department_id, salary,avgsal
from employees e,(select department_id,round(avg(salary)) avgsal
from employees
group by department_id) avg_sal
where e.department_id = avg_sal.department_id
and e.salary > avg_sal.avgsal
order by e.department_id;
3，单列子查询的其他应用
在主查询的select列表中使用
问题：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，
则location为’Canada’,其余则为’USA’。
select employee_id,last_name,department_id,
(case when department_id = (select department_id
from departments
where location_id = 1800
)
then 'USA'
else 'Canada' end) location
from employees
order by department_id;
或
select employee_id,last_name,department_id,
(case department_id when (select department_id
from departments
where location_id = 1800
)
then 'USA'
else 'Canada' end) location
from employees
order by department_id;
或
select employee_id,last_name,department_id,
decode(department_id,(select department_id
from departments
where location_id = 1800
),'USA',
'Canada') location
from employees
order by department_id;
4，相关子查询，
子查询语句中使用了主查询语句中的表中的数据（这个数据不一定在主查询的select语句中）
相关子查询按照一行接一行的顺序执行，即子查询取主查询表中的每一行数据值，主查询的每一行都执行一次子查询。
使用相关子查询，要考虑是否必须使用、是否合理，否则在不需要使用相关子查询的情况下就能轻易得到查询结果，使用相关子查询反而会使查询的效率下降。
用法：
get：从主查询所用的表中获取候选列
execute：子查询使用主查询的数据，并进行相关的筛选
use：如果满足内查询的条件则返回值
例1：查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id,last_name,e.department_id,department_name
from employees e,departments dd
where e.department_id = dd.department_id(+) --该查询返回employees表中的所有行，没返回一行，都会取一个employee_id到子查询中
order by (select department_name
from departments d
where e.department_id = d.department_id); --子查询根据主查询返回的employee_id，在departments表中进行查找，有则返回数据。
desc;
例2：查询员工中工资大于本部门平均工资的员工的last_name,salary,department_id和本部门的平均工资
select last_name,salary,e1.department_id,ss.avgsal
from employees e1,(select department_id,round(avg(salary)) avgsal
from employees
group by department_id) ss --该from字句同前例
where e1.department_id = ss.department_id
and salary > (
select round(avg(salary))
from employees e2
where e1.department_id = e2.department_id --将外查询中的department_id，查找该处的department_id，并将所有的返回结果分组
group by e2.department_id)
order by e1.department_id;
例3：若employees表中employee_id与job_history表中employee_id相同且job_history表中employee_id的数目不小于2，
输出employees中这些相同id的员工的employee_id,last_name和其job_id
select e1.employee_id ,last_name,e1.job_id
from employees e1
where (select count(job_id)
from job_history j1
where e1.employee_id = j1.employee_id) >= 2
5，exists
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 TRUE
如果在子查询中不存在满足条件的行:
条件返回 FALSE
继续在子查询中查找
查询公司管理者的employee_id,last_name,job_id,department_id信息
子查询
select employee_id,last_name,job_id,department_id
from employees e1
where e1.employee_id in (
select distinct(manager_id)
from employees);
自连接
select distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id
from employees e1,employees e2
where e1.employee_id = e2.manager_id;
相关子查询
select employee_id,last_name,job_id,department_id
from employees e1
where e1.employee_id in (
select manager_id
from employees e2
where e1.employee_id = e2.manager_id);
使用exists相关子查询
select employee_id,last_name,job_id,department_id
from employees e1
where exists (
select 'A' --根据e1表中的每个 employee_id在e2中查找 manager_id,如果找到，子查询返回true，主查询返回该数据行
from employees e2
where e1.employee_id = e2.manager_id);
6.not exists
查询departments表中，不存在于employees表中的部门的department_id和department_name
select d.department_id,d.department_name
from departments d
where not exists ( --not exists 返回子查询中false的结果
select 'X' --根据e1表中的每个 employee_id在e2中查找 manager_id,如果找到，子查询返回true，即该部门在departments中存在，也有员工
from employees e
where d.department_id = e.department_id);
7.相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据
例：向employees中添加一列 department_name ，并更具department_id填充
update employees e1
set department_name = (
select department_name
from departments
where e1.department_id = department_id);
8,相关删除
使用相关子查询依据一个表中的数据删除另一个表的数据
例：删除表employees中，其与emp_history表皆有的数据
delete from employees e1
where employee_id in (
select employee_id
from job_history
where e1.employee_id = employee_id);
9,使用 WITH 子句
可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率
例，查询公司中各部门的总工资,大于公司中各部门的平均总工资,的部门信息
使用普通方法：使用普通方法，汇总后的语句冗长不易理解
--各部门总工资
select department_id,sum(salary) sum_sal from employees group by department_id;
--各部门的平均总工资
select sum(to_sal.sum_sal)/count(*) to_avg_sal
from (select department_id,sum(salary) sum_sal
from employees group by department_id) to_sal;
--根据前面两项，查询最终结果
select to_sal.department_id,sum_sal
from (select department_id,sum(salary) sum_sal from employees group by department_id) to_sal,
(select sum(to_sal.sum_sal)/count(*) to_avg_sal from (select department_id,sum(salary) sum_sal from employees group by department_id) to_sal) avg_sal
where to_sal.sum_sal > avg_sal.to_avg_sal;
使用with字句
with to_sal as --各部门总工资
(select department_id,sum(salary) sum_sal from employees group by department_id),
avg_sal as --各部门的平均总工资
(select sum(to_sal.sum_sal)/count(*) to_avg_sal from to_sal)
select department_id,sum_sal
from to_sal
where to_sal.sum_sal > (
select avg_sal.to_avg_sal
from avg_sal);
with
to_sal as
(select department_id,sum(salary) sum_sal from employees group by department_id),
avg_sal as
(select sum(to_sal.sum_sal)/count(*) to_avg_sal from to_sal)
select department_id,sum_sal,to_avg_sal
from to_sal,avg_sal
where to_sal.sum_sal > avg_sal.to_avg_sal;
注明：本博文系学习尚硅谷网易云课堂教学课程总结输出。
