Oracle子查询详解

1.先于主查询执行。

2.主查询调用了子查询的结果。

3.注意列的个数和类型要匹配。

4.查询返回多行要用多行关系运算操作。

5.子查询要用括号括起来。

查询工资总和高于10号部门工资总和的部门。

select deptno,sum(sal)

from emp

group by deptno

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

DEPTNO SUM(SAL)

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

30 9400

查询每个部门的最大工资是谁。

select deptno,ename,sal

from emp

where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

DEPTNO ENAME SAL

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

30 BLAKE 2850

10 KING 5000

20 FORD 3000

子查询返回多行，用=不可以，得用in。

子查询返回多列，所以对比的列也要匹配。

Any 和all操作

SQL> select ename,sal from emp where sal<any(1000,2000);

ENAME SAL

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

SMITH 800

ALLEN 1600

WARD 1250

MARTIN 1250

TURNER 1500

JAMES 950

MILLER 1300

7 rows selected.

小于2000就可以

SQL> select ename,sal from emp where sal<all(1000,2000);

ENAME SAL

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

SMITH 800

JAMES 950

必须小于1000

小于all小于最小，大于all大于最大

SQL> select ename,sal,deptno from emp

2 where sal<all(select avg(sal) from emp group by deptno);

ENAME SAL DEPTNO

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

SMITH 800 20

WARD 1250 30

MARTIN 1250 30

TURNER 1500 30

JAMES 950 30

MILLER 1300 10

小于any小于最大，大于any大于最小

select ename,sal,deptno from emp

where sal>any(select avg(sal) from emp group by deptno)

ENAME SAL DEPTNO

ALLEN 1600 30

JONES 2975 20

BLAKE 2850 30

CLARK 2450 10

KING 5000 10

FORD 3000 20

查询工资大于本部门平均工资的员工。

select ename,e.deptno,sal,asal

from emp e,

(select deptno ,avg(sal) asal from emp group by deptno) a

where e.deptno=a.deptno and sal>asal;

A为视图，为什么要使用别名asal,因为表达式不能当列的名称，别名的本质使用方法是使非法的合法化。

ENAME DEPTNO SAL ASAL

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

BLAKE 30 2850 1566.66667

ALLEN 30 1600 1566.66667

FORD 20 3000 2258.33333

JONES 20 2975 2258.33333

KING 10 5000 2916.66667

select ename,sal ,deptno

from emp o

where sal>(select avg(sal) from emp where deptno=o.deptno) ;

ENAME SAL DEPTNO

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

ALLEN 1600 30

JONES 2975 20

BLAKE 2850 30

KING 5000 10

FORD 3000 20

先运行主查询，得到第一行，将DEPTNO传入到子查询，由子查询求出AVG（SAL），在判定主查询的行是否符合查询的条件。

执行计划是将子查询看作视图的关联。这叫做SQL的自动改写。

select ename,empno,mgr from emp o

where exists(select 3 from emp where mgr=o.empno);

ENAME EMPNO MGR

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

FORD 7902 7566

BLAKE 7698 7839

KING 7839

JONES 7566 7839

CLARK 7782 7839

找领导，其中3是常量，你写什么都可以。

当子查询有行时，Exists返回true

查到行后就不再继续查询

当子查询没有行时为假，Exists返回false

搜索刘老师微信号：Rman-2014，备注“Oracle学习与咨询”，即可添加好友；或者扫描下面二维码，关注我的“微学堂”公众号，了解最新OCP认证动态、题库及答案解析、培训机构及讲师介绍、课堂授课内容等。每天还有一篇技术文章发布哦!