一、WHERE條件中的子查詢

創(chuàng)新互聯(lián)-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比東鄉(xiāng)網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式東鄉(xiāng)網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋東鄉(xiāng)地區(qū)。費(fèi)用合理售后完善，10年實(shí)體公司更值得信賴。

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. 當(dāng)all后面接子查詢的時(shí)候

"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.至少有一個(gè)值不匹配

"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 大于任何一個(gè)，那不就是最小的么？？,任意一個(gè)

5. 當(dāng)any后面接子查詢的時(shí)候

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

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

"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. 工資高于本部門平均工資的人（拿上游工資的人）有哪些？

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

    ②使用emp表和平均工資表進(jìn)行關(guān)聯(lián)，

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 

特點(diǎn)：先有結(jié)果集在有rownum，是對(duì)結(jié)果集的一個(gè)編號(hào)

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

    ①先把工資排序

    ②在使用rownum限結(jié)果集（為什么不在第一步就使用rownum限定結(jié)果集？執(zhí)行順序的問題，where要比order by先執(zhí)行，獲取rownum<6的時(shí)候還沒來得及排序在從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列，這樣就又構(gòu)造成一個(gè)結(jié)果集

    ③把上一個(gè)結(jié)果集中id為6~10的條目拿出來

    ④和emp關(guān)聯(lián)

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;