Oracle-子查詢

一、WHERE條件中的子查詢

1. 比black工資高的雇員有哪些?

select ename 

from emp

where sal>(select sal from emp where ename='BLAKE');

2. 高于30部門最高工資的雇員有哪些?

select ename,sal 

from emp

where sal>(select max(sal) from emp where deptno=30);

select ename,sal 

from emp

where sal > all (select sal from emp where deptno=10);   --任何

3. 當all后面接子查詢的時候

"x = ALL (...)": The value must match all the values in the list to evaluate to TRUE.所有值都要匹配

"x != ALL (...)": The value must not match any values in the list to evaluate to TRUE.至少有一個值不匹配

"x > ALL (...)": The value must be greater than the biggest value in the list to evaluate to TRUE.大于最大的值

"x < ALL (...)": The value must be smaller than the smallest value in the list to evaluate to TRUE.小于最小的值

"x >= ALL (...)": The value must be greater than or equal to the biggest value in the list to evaluate to TRUE.大于等于最大的值

"x <= ALL (...)": The value must be smaller than or equal to the smallest value in the list to evaluate to TRUE.小于等于最小的值

4. 大于10部門最小工資的雇員有哪些?

select ename,sal 

from emp

where sal> (select min(sal) from emp where deptno=10);

select ename,sal 

from emp

where sal > any (select sal from emp where deptno=10);   --any 大于任何一個，那不就是最小的么？？,任意一個

5. 當any后面接子查詢的時候

"x = ANY (...)": The value must match one or more values in the list to evaluate to TRUE.至少匹配一個值

"x != ANY (...)": The value must not match one or more values in the list to evaluate to TRUE.一個值都不匹配

"x > ANY (...)": The value must be greater than the smallest value in the list to evaluate to TRUE.大于最小值

"x < ANY (...)": The value must be smaller than the biggest value in the list to evaluate to TRUE.小于最大值

"x >= ANY (...)": The value must be greater than or equal to the smallest value in the list to evaluate to TRUE.大于等于最小值

"x <= ANY (...)": The value must be smaller than or equal to the biggest value in the list to evaluate to TRUE.小于等于最大值

6. 工資最高的人是誰?

select ename from emp

where sal=(select max(sal) from emp);

7. 和ALLEN同部門，工資高于MARTIN的雇員有哪些?

select ename from emp

where deptno=(select deptno from emp where ename='ALLEN')

and sal>(select sal from emp where ename='MARTIN');

8. 工作和部門與SMITH相同，工資高于JAMES的雇員有哪些?

select ename from emp

where (job,deptno)=(select job,deptno from emp where ename='SMITH')

and sal>(select sal from emp where ename='JAMES');

二、FROM子句中的子查詢

1. 工資高于本部門平均工資的人（拿上游工資的人）有哪些？

    ①求出每個部門的平均工資，把這個作為一張表

    ②使用emp表和平均工資表進行關聯，

select ename,sal,avgsal,e.deptno

from emp e,

(select avg(sal) avgsal,deptno 

from emp

group by deptno) b

where e.deptno=b.deptno

and e.sal>b.avgsal;

三、偽列：rownum 

特點：先有結果集在有rownum，是對結果集的一個編號

1. 工資前五名的人?(TOP-N 分析)

    ①先把工資排序

    ②在使用rownum限結果集（為什么不在第一步就使用rownum限定結果集？執行順序的問題，where要比order by先執行，獲取rownum<6的時候還沒來得及排序在從emp里面拿出來

select ename,sal

from emp

where sal in

(select sal 

from (select distinct sal from emp order by sal desc)

where rownum<6)

order by sal desc;

3. 工資6~10的人?

    ①先把工資排序

    ②把工資排名在6~10的拿出來，由于不能使用rownum>6 and xxx<10這樣，所以要加一步，把rownum變成id列，這樣就又構造成一個結果集

    ③把上一個結果集中id為6~10的條目拿出來

    ④和emp關聯

select ename,sal from emp 

where sal in 

(select sal from 

(select rownum rn,sal 

from (select distinct sal 

      from emp order by sal desc))

where rn between 6 and 10)

order by sal desc;