Oracle子查詢（巢狀查詢）

標籤：

概念：

所謂子查詢，即一個select語句中嵌套了另外的一個或者多個select語句

 

需求：尋找和Smith同部門的所有員工的id和last_name

目標： 員工id,last_name

from:  s_emp

條件： s_emp.dept_id = Smith所在部門的id？

select id,last_name

from s_emp

where dept_id = ?

 

階段目標： Smith所在部門的id

目標： dept_id

from : s_emp

條件： last_name = ‘Smith‘;

select dept_id

from s_emp

where last_name = ‘Smith‘;

 

組合：

select id,last_name

from s_emp

where dept_id = (

select dept_id

from s_emp

where last_name = ‘Smith‘

)

 

應用情境：

1.一條查詢語句的查詢條件依賴另外一條查詢語句的查詢結果。

2.一條查詢語句的查詢結果是作為另外一條查詢語句的查詢表(查詢依據)。

3.在DML操作中使用子查詢(後期介紹)

 

子查詢的基本原則：

1.在查詢中可以有單行子查詢和多行子查詢

2.子查詢可以出現在操作符的左邊或者右邊

3.子查詢在很多sql命令中都可以使用

4.巢狀查詢先執行，然後將結果傳遞給主查詢。

 

 

一、比較值不確定，需要另外一個select語句執行後才能得到，使用子查詢

文法：

select ...

from ...

where col_name 比較操作符 (

select ...

from ...

where ...

group by ...

having...

)

group by ...

having...

order by ...

 

單值子查詢：子查詢的結果為1個

需求：

1.查詢和Simith職稱相同的所有員工的last_name和職稱

分析步驟：

1.確定最終查詢結果(目標/主查詢)：查詢員工的last_name和title

  from : s_emp

  條件 ： title = Smith的職稱

select last_name,title

from s_emp

where title = ?

2.確定條件(子查詢)：Smith的職稱

from : s_emp

條件 ：last_name = ‘Smith‘;

select title

from s_emp

where last_name = ‘Smith‘;

3.組合

select last_name,title

from s_emp

where title = (select title

from s_emp

where last_name = ‘Smith‘);

2.查看工資大於Chang員工工資的所有員工的id和名字。

最終目標：員工的id,last_name

from：s_emp

條件： salary > Chang員工的工資

select id,last_name

from s_emp

where salary > ?

 

階段目標：Chang員工的工資

from ： s_emp

條件： last_name = ‘Chang‘;

select salary

from s_emp

where last_name = ‘Chang‘;

 

組合：

 

 

3.查看員工工資小於平均工資的所有員工的id和名字

 

例如：尋找和Smith同一個部門的員工的id和last_name

 

 

多值子查詢：子查詢的結果為多個

需求：

1.查詢所在地區為2號地區的所有部門的員工的id和last_name

 1.確定最終查詢結果： 員工的id, last_name

   from : s_emp

   條件 ：s_emp.dept_id  in (?);

select id,last_name

from s_emp

where dept_id  in ?

 2.確定條件：所在地區為2號部門id

子查詢：部門id

from : s_dept

條件: region_id = 2;

select id

from s_dept

where region_id = 2;

 3.組合：

select id,last_name

from s_emp

where dept_id  in (

select id

from s_dept

where region_id = 2

)

 

 

子查詢出現情況二：

尋找的內容不確定，需要從構建出來一個查詢的表

文法：

select ....

from (select .... from ....) b

where ......

 

練習：查詢各部門的id，name 和部門員工的平均工資

 

1.查詢目標：

需要部門的id，部門的name ------ 從 s_dept表中

部門員工的平均工資 avg(salary) --------- salary只有s_emp表中有

條件 ： 部門id，name和部門 員工，因此要求部門的id跟員工所在部門的id相等才串連

select id,name, 平均工資

from s_dept , ?

where s_dept.id = ?.dept_id;

 

2.查詢條件

select(dept_id,avg(salary) sal)

from s_emp

group by dept_id;

 

3.組合：

select id,name,b.sal

from s_dept dept,(select dept_id,avg(salary) sal

from s_emp

group by dept_id

) b

where dept.id = b.dept_id; 

Oracle子查詢（巢狀查詢）