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

超、凢脫俗 2021-08-13 13:39 1026阅读 0赞

#### 什么是子查询？ ####

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

#### 子查询的语法 ####

![在这里插入图片描述][20190910225834967.png]

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

#### 子查询的类型 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

#### 单行子查询 ####

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

<table> 
 <thead> 
  <tr> 
   <th align="center">操作符</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">=</td> 
   <td align="center">Equal to</td> 
  </tr> 
  <tr> 
   <td align="center">&gt;</td> 
   <td align="center">Greater than</td> 
  </tr> 
  <tr> 
   <td align="center">&gt;=</td> 
   <td align="center">Greater than or equal to</td> 
  </tr> 
  <tr> 
   <td align="center">&lt;</td> 
   <td align="center">Less than</td> 
  </tr> 
  <tr> 
   <td align="center">&lt;=</td> 
   <td align="center">Less than or equal to</td> 
  </tr> 
  <tr> 
   <td align="center">&lt;&gt;</td> 
   <td align="center">Not equal to</td> 
  </tr> 
 </tbody> 
</table>

示例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);

![在这里插入图片描述][20190911213229706.png]  
示例2：

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

![在这里插入图片描述][20190911213418469.png]  
示例3：

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

![在这里插入图片描述][20190911213939330.png]  
示例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);

![在这里插入图片描述][20190911214544504.png]  
示例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;

![在这里插入图片描述][2019091121491058.png]

#### 非法使用单行子查询示例 ####

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

![在这里插入图片描述][20190911215030592.png]

#### 多行子查询 ####

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

<table> 
 <thead> 
  <tr> 
   <th align="center">操作符</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">IN</td> 
   <td align="center">等于列表中的任何一个</td> 
  </tr> 
  <tr> 
   <td align="center">ANY</td> 
   <td align="center">和子查询返回的任意一个值比较</td> 
  </tr> 
  <tr> 
   <td align="center">ALL</td> 
   <td align="center">和子查询返回的所有值比较</td> 
  </tr> 
 </tbody> 
</table>

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

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

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

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

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

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

![在这里插入图片描述][20190911231958103.png]

#### 子查询需要注意的问题 ####

*  括号
 *  合理的书写风格
 *  可以在主查询的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;

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

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

![在这里插入图片描述][20190911230021619.png]  
示例：top-n分析问题  
rownum行号：

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

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

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

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

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;

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

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);

![在这里插入图片描述][20190911234258807.png]

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

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;

![在这里插入图片描述][20190911235654533.png]

#### 子查询中的null值问题 ####

*  单行子查询中的null值问题  
    ![在这里插入图片描述][20190911232058329.png]\- 多行子查询中的null值问题  
    示例：查询不是老板的员工  
    ![在这里插入图片描述][20190911232127499.png]![在这里插入图片描述][20190911232239932.png]查询错误，集合中有null值不可以使用not in

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/7c73070292d449339e7e5cc727d57692.png
[20190910225834967.png]: /images/20210813/ec28709c60064469b2c61ad95ad13477.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/5b6d101703f94cdfa48d82e93778d48c.png
[20190911213229706.png]: /images/20210813/ddb50b90df694dfa882c3bef99c84765.png
[20190911213418469.png]: /images/20210813/2cd57dd8c2ca4bdf9db9f2de3abc98f5.png
[20190911213939330.png]: /images/20210813/041a09b6ed324f85a1a91fcf3a1540b3.png
[20190911214544504.png]: /images/20210813/63a888430a6b4c0993351c6ef0daa4af.png
[2019091121491058.png]: /images/20210813/45a5c5f6f4c34bcc845baa060c441d50.png
[20190911215030592.png]: /images/20210813/74fd6e73029a411fa9d3c2b1404bd752.png
[20190911231452506.png]: /images/20210813/ce641f0bef754faab0fbe6381f5b3c2b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/5b54dfddf48a4c798e4d093f5a9230be.png
[20190911231958103.png]: /images/20210813/78800f9dc4ee46c09bc50ceb3d2371fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/ac04802363c24e0181698a390e7c675f.png
[20190911230021619.png]: /images/20210813/63fe45024b47457d8bc776c3d3af4aa5.png
[20190911233127490.png]: /images/20210813/89361f0ead724971be37dfb1b5c9d3c3.png
[20190911233641685.png]: /images/20210813/c19e6d31dbd04504a69d1e5ef2516b4b.png
[20190911234258807.png]: /images/20210813/bfadae9cfd4449f3a0fb9a958c1b22dc.png
[20190911235654533.png]: /images/20210813/12e75e4ac0614d8f8ab835de4789e676.png
[20190911232058329.png]: /images/20210813/c9c9488f71ad4fed8bf32be1631b2dfe.png
[20190911232127499.png]: https://img-blog.csdnimg.cn/20190911232127499.png
[20190911232239932.png]: /images/20210813/3aaff0ab1e90462da3af6ab3b5462830.png