oracle数据库SQL开发之子查询

一、子查询

1.-括号内的查询叫做子查询，也叫内部查询，先于主查询执行。子查询的结果被主查询（外部查询）使用
包括比较运算符。
– 单行运算符：>、=、>=、<、<>、<=
– 多行运算符： in、any、all

2.子查询可以嵌于以下SQL子句中：
– where子句
– having子句
– from子句

二、单行子查询

• 子查询只返回一行一列
• 使用单行运算符 >、=、>=、<、<>、<=。

三、多行子查询

1. IN操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。

2. any：表示和子查询的任意一行结果进行比较，有一个满足条件即可。
   • < any()：表示小于子查询结果集中的任意一个，即小于最大值就可以。
   • > any()：表示大于子查询结果集中的任意一个，即大于最小值就可以。
   • = any()：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN.

3. all：表示和子查询的所有行结果进行比较，每一行必须都满足条件。。
   • < all():表示小于子查询结果集中的所有行，即小于最小值。
   • > all():表示大于子查询结果集中的所有行，即大于最大值。
   • = all() :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

四、多列子查询

1. -查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、部门、职位入职日期,不包括1981年入职员工（where子句中 一起括起来用in）

SQL> SELECT ename, deptno, job, hiredate
              FROM emp
              WHERE (deptno, job) IN
                           (SELECT deptno,job
                          FROM emp
                         WHERE to_char(hiredate,'YYYY')='1981')
            AND to_char(hiredate,'YYYY')<>'1981';

2.-查询出和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期,不包括1981年入职员工。（where 子句中 用or）

SQL> SELECT ename, deptno, job, hiredate
		FROM emp
 		WHERE (deptno IN (SELECT deptno
 		FROM emp
		 WHERE to_char(hiredate,'YYYY')='1981')
		 OR job IN (SELECT  job
		 FROM emp
 		WHERE to_char(hiredate,'YYYY')='1981'))
		 AND to_char(hiredate,'YYYY')<>'1981');

3.因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符（所以记得去除子查询结果中的空值select emp.mgr from emp where mgr is not null）。
–• 3.查询不是经理的员工姓名。

SQL>select emp.ename 不是经理的员工姓名
	from emp
	where emp.empno not in(select emp.mgr from emp where mgr is not null);

4.在 FROM 子句中使用子查询
• 查询比自己/本部门平均工资高的员工姓名，工资，部门编号，部门平均工资

SQL> SELECT a.ename, a.sal, a.deptno, b.salavg
         FROM emp a,(SELECT deptno, avg(sal) salavg
                                   FROM emp
                                 GROUP BY deptno) b
          WHERE a.deptno = b.deptno
          AND a.sal > b.salavg;

五、伪列rownum 虚表dual

– rownum是一个伪列，伪列是使用上类似于表中的列，而实际并没有存储在表中的特殊列；

–rownum的功能是在每次查询时，返回结果集的行的顺序号，这个顺序号是在记录输出时才一步一步产生的，第一行显示为1，第二行为2，以此类推。

– rownum使用的注意点：

1. 对于rownum只能执行<、<=运算，不能执行>、>=或一个区间运算Between…And等
2. rownum和order by 一起使用时，–rownum与order by 一起使用，序号打乱 因为rownum在记录输出时生成，而order by子句在最后执行， 所以当两者一起使用时， 需要注意rownum实际是已经被排了序的rownum。

• 查询入职日期最早的前五名员工

SQL>SELECT rownum,a.*
            FROM
            (     SELECT rownum,ename,hiredate
                  FROM emp
                  ORDER BY hiredate ) a
            WHERE ROWNUM <= 5;

六、top查询 分页

– Top-N查询主要是实现表中按照某个列排序，输出最大或最小的N条记录功能。

• Top-N分析语法

1.不排序的分页查询

select a.*
from
(select rownum rn,ename,hiredate,job
        from emp
        where  rownum <=目标页数*每页记录数) a
where rn>（目标页数-1）*每页记录数

2.排序的分页查询

select b.*
from
    (select rownum rn, a.*
    from
       ( select ename,empno,sal
        from emp
        order by sal desc) a   --第一步子查询排序   
    where rownum <= 目标页数*每页记录数 --第二步子查询选出前几个，只能用<=号
        ) b
where rn > （目标页数-1）*每页记录数