Oracle--子查询(单行子查询、多行子查询、相关子查询)详解

关注

原创最新推荐文章于 2026-09-20 10:08:56 发布 2.3k 阅读 AI 写同款· GEO 优化 ›

Oracle数据库SQL语句（四）—子查询（嵌套查询） Oracle数据库SQL语句（四）一、子查询（嵌套查询） 1、单行子查询 SELECT * FROM stu WHERE ID > (SELECT ID FROM stu WHERE NAME ='王五'); 2、多行子查询 --IN 在什么范围内 SELECT * FROM stu WHERE ID IN (SELECT ID FROM stu WHERE CLASS = '一班'); --ANY：表示任意的 --= ANY ：和子查询中任意一个结果相等阅读详情

什么是子查询？

使用子查询解决问题：谁的工资比SCOTT高？
在这里插入图片描述

子查询的语法

在这里插入图片描述

$子查询（内查询）在主查询之间一次执行完成。\color{red}{子查询（内查询）在主查询之间一次执行完成。}$
$子查询的结果被主查询使用（外查询）。\color{red}{子查询的结果被主查询使用（外查询）。}$

子查询的类型

在这里插入图片描述

单行子查询

只返回一条记录
单行操作符

操作符	含义
=	Equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
<>	Not equal to

示例1：

select ename,job,sal
from emp
where job=
      (select job
      from emp
      where empno=7566) and sal >
            (select sal
            from emp
            where empno=7782);

在这里插入图片描述
示例2：

select ename,job,sal
from emp
where sal=
     (select min(sal)
     from emp);

在这里插入图片描述
示例3：

select deptno,min(sal)
from emp
group by deptno
having min(sal) >
      (select min(sal)
      from emp
      where deptno=20);

在这里插入图片描述
示例4：
查询出比雇员7654的工资高，同时从事和7788的工作一样的员工

select *
from emp t1
where t1.sal>
      (select t.sal 
      from emp t 
      where t.empno=7654) and t1.job=
            (select t2.job
            from emp t2
            where t2.empno=7654);

在这里插入图片描述
示例5：
要求查询每个部门的最低工资和最低工资的雇员和部门名称

 select d.dname, a.minsal, e.ename
 from dept d,
      (select deptno, min(sal) minsal 
      from emp
      group by deptno) a,emp e
 where d.deptno=a.deptno and e.sal=a.minsal;

在这里插入图片描述

非法使用单行子查询示例

select empno,ename
from emp
where sal=
     (select min(sal)
     from emp
     group by deptno);

在这里插入图片描述

多行子查询

返回了多条记录
多行操作符

操作符	含义
IN	等于列表中的任何一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

示例：in 在集合中
查询部门名称是SALES和ACOUNTING的员工

select * from emp where deptno in
       (select deptno from dept where dname='SALES' or dname='ACCOUNTING');

在这里插入图片描述
示例：any 和集合中的任意一个值比较
查询工资比30号部门员工高的员工信息

select * from emp where sal > any
       (select sal from emp where deptno=30);

在这里插入图片描述
示例：all 和集合中的所有值比较
查询工资比30号部门所有员工高的员工信息

select * from emp where sal > all
       (select sal from emp where deptno=30);

在这里插入图片描述

子查询需要注意的问题

括号
合理的书写风格
可以在主查询的where、select、having、from后面使用子查询
不可以在group by使用子查询
强调from后面的子查询
主查询和子查询可以不是同一张表；只有子查询返回的结果主查询可以使用即可
一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
一般先执行子查询，再执行主查询；相关子查询列外
单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
子查询中的null

示例：在select语句后使用子查询
注意：select语句后的子查询必须是单行子查询
查询员工号，姓名，薪水，7839的职位

select empno,ename,sal,
       (select job 
       from emp 
       where empno=7839)
from emp;

在这里插入图片描述
示例：在from语句后面使用子查询
查询员工信息：员工号，姓名，月薪

select * 
from (select empno, ename, sal from emp);

在这里插入图片描述
示例：top-n分析问题
rownum行号：

rownum永远按照默认的顺序生成
rownum只能使用小于等于，不能使用大于等于

查询员工表中工资最高的前三名

select rownum, empno, ename, sal
from (select * from emp order by sal desc)
where rownum <= 3;

在这里插入图片描述
示例：找到员工表中薪水大于本部门平均薪水的员工

select e.empno, e.ename, e.sal, d.avgsal
from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) d
where e. deptno=d.deptno and e.sal > d.avgsal;

在这里插入图片描述
相关子查询：
将主查询中的值作为参数传递给子查询
使用相关子查询解决上面的示例

select empno, ename, sal, (select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno=e.deptno);

在这里插入图片描述

示例：统计每年入职员工的个数

select count(*) Total,
       sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) "1980",
       sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) "1981",
       sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) "1982",
       sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) "1987"
from emp;