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;