使用关联相关子查询
========================
虽然每个表只对一个子查询求值一次,但是对每一行只对一个相关的子查询求值一次。
子查询可以从外部查询中获取价值。
示例#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