Oracle 高級子查詢 高級子查詢相對於簡單子查詢來說，返回的數據行不再是一列，而是多列數據。 1，多列子查詢 主查詢與子查詢返回的多個列進行比較 查詢與141號或174號員工的manager_id和department_id相同的其他員工的employee_id, manager_id, dep ...
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;
註明：本博文系學習尚矽谷網易雲課堂教學課程總結輸出。
