使用子查询-2

使用关联相关子查询

========================

虽然每个表只对一个子查询求值一次，但是对每一行只对一个相关的子查询求值一次。

子查询可以从外部查询中获取价值。

示例＃1

---------

从表中删除重复记录

Delete from emp e  where rowid>(select min(rowid) from emp where e.empno=empno) 

要么

Delete from emp 
where rowid not in(select max(rowid) from emp group  by empno) 

示例＃2

------------

显示员工的第一位

select * from emp e where 0=(select count(distinct(sal)) from emp
where sal>e.sal) order by sal desc 

示例＃3

------------

显示工资高于其所属部门平均工资的员工的详细信息

Select * from emp e
Where sal>(select avg(sal) from emp where deptno=e.deptno) 

多行子查询

=======================

内部查询返回多行

使用多行比较运算符

IN ----等于列表中的任何成员

ANY-将值与子查询返回的每个值进行比较。

ALL-将值与子查询返回的每个值进行比较。

<ANY表示小于最大值

> ANY的含义超出最小值

= ANY等于IN

> ALL意味着大于最大值

<ALL表示小于最小值。

NOT运算符可以与IN，ANY和ALL运算符一起使用

使用IN

==========

例子1

-------------------

查找获得与部门最低工资相同工资的员工。

SELECT * FROM emp
WHERE sal IN ( SELECT MIN(sal) FROM emp GROUP BY deptno); 
is  same as 
SELECT * FROM emp WHERE sal IN (800, 950, 1300); 

使用任何

=============

ANY运算符（及其同义词SOME运算符）将一个值与子查询返回的每个值进行比较。

范例＃2

--------------------------

显示薪水低于任何业务员且不是职员的员工。

SELECT * FROM emp  WHERE sal < ANY ( SELECT sal FROM  emp WHERE job= 'CLERK')  AND job <> 'CLERK'; 

使用全部

============

ALL运算符将一个值与子查询返回的每个值进行比较。

例子＃3

--------------------------

显示其工资高于所有部门平均工资的员工的详细信息

SELECT * FROM emp WHERE sal > ALL(SELECT avg(sal)  FROM emp GROUP BY deptno); 

上面的示例显示的员工的薪水高于所有部门的平均薪水。 部门的最高平均薪水是2916.66，因此查询返回薪水大于2916.66的那些雇员

注意：-请尝试使用Scott Schema

使用内联子查询（内联视图）

=============================

示例＃1

----------

显示奇数记录。

SELECT  * FROM  ( SELECT rownum rn, empno, ename FROM emp)  temp
 WHERE  MOD(temp.rn,2) = 1 

示例＃2

----------

展示经理及其薪水

SELECT A.MGR, B.SAL FROM (SELECT DISTINCT  MGR FROM EMP
 WHERE MGR IS NOT NULL) A   LEFT OUTER JOIN 
 (SELECT EMPNO, SAL FROM EMP) B  ON  A.MGR=B.EMPNO 

示例＃3

----------

收入最高的前三名员工。

select * from (select  * from emp order by sal where rownum<4 

示例＃4

----------

查询列表Deptno，ename，sal，SUM（该部门中的sal）：

 
SELECT deptno, ename, sal, (SELECT SUM(sal) FROM emp WHERE a.deptno =deptno) FROM emp a  ORDER BY deptno 

示例＃5

----------

显示经理的编号和姓名，还有更多员工

select empno,ename from emp where empno=(select mgr from 
(select mgr,count(empno) from emp  where mgr is not null group by mgr
order by count(empno) desc) where rownum<2) 

From: https://bytes.com/topic/oracle/insights/739009-using-subqueries-2-a