第七章 子查询

子查询：

 

跟一个不确定的条件去比，使用子查询

 

使用子查询 

 

    SELECT      select_list 

    FROM        table 

    WHERE       expr operator 

                                (SELECT           select_list 

                                FROM              table); 

 

   • 括号内的查询叫做子查询，也叫内部查询，先 

      于主查询执行。 

   • 子查询的结果被主查询（外部查询）使用 

   • expr operator包括比较运算符。 

        –单行运算符：>、=、>=、<、<>、<= 

        –多行运算符： IN、ANY、ALL 

 

 

使用子查询 

• 子查询可以嵌于以下SQL子句中： 

        –WHERE子句 

        –HAVING子句 

        –FROM子句 

使用子查询 

 

   • 子查询使用指导 

          –子查询要用括号括起来 

          –将子查询放在比较运算符的右边 

          –对于单行子查询要使用单行运算符 

          –对于多行子查询要使用多行运算符 

单行子查询：一行一列

多行子查询：多行（0,1，。。）一列

多行子查询 

     –子查询返回记录的条数 可以是一条或多条。 

     –和多行子查询进行比较时，需要使用多行操作符，多 

       行操作符包括： 

         •IN 

         •ANY 

         •ALL 

 

     –IN操作符和以前介绍的功能一致，判断是否与子查询 

        的任意一个返回值相同。 

IN: IN使用 

 

  SELECT empno,ename 

  FROM      emp 

  WHERE     sal = (应该换成IN)

                         (SELECT       MIN(sal) 

                          FROM         emp 

                          GROUP BY deptno); 

 

     –返回结果 

         ERROR at line 4: 

         ORA-01427: single-row subquery returns more than 

           one row 

 

ANY

–ANY：表示和子查询的任意一行结果进行比较，有一个满足 

       条件即可。 

         •< ANY：表示小于子查询结果集中的任意一个，即小于最 

           大值就可以。 

 

         •> ANY：表示大于子查询结果集中的任意一个，即大于最 

           小值就可以。 

 

         •= ANY：表示等于子查询结果中的任意一个，即等于谁都 

           可以，相当于IN。 

 

ALL

ALL的使用 

     –ALL：表示和子查询的所有行结果进行比较，每一行必须都 

       满足条件。 

         •< ALL:表示小于子查询结果集中的所有行，即小于最小 

           值。 

 

         •> ALL:表示大于子查询结果集中的所有行，即大于最大 

           值。 

 

         •= ALL :表示等于子查询结果集中的所有行,即等于所有 

           值，通常无意义。 

练习2 

 

• 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工 

-------1
SELECT ename,hiredate
FROM emp
WHERE hiredate >
ANY (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno<>10

• 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工 

SELECT ename,hiredate
FROM emp
WHERE hiredate >
ALL (SELECT hiredate FROM emp WHERE deptno=10)
AND deptno<>10

• 3.查询职位和10部门任意一个员工职位相同的员 工姓名，职位，不包括10部门员工 

SELECT ename,job
FROM emp
WHERE job=
ANY (SELECT job FROM emp WHERE deptno=10)
AND deptno<>10

--------------------------
----------------------------------
------------------------------------
--------查询每个部门的最低工资的员工的信息
SELECT * FROM emp
WHERE (sal,deptno) IN
(SELECT MIN(sal),deptno FROM emp GROUP BY deptno)-----（要一一对应）

 

----------
查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资
思路：假设有一个表，保存部门的平均工资和部门编号（嵌套子查询）

SELECT ename,sal, emp.deptno,avgsal
FROM emp,(
          SELECT deptno,AVG(sal) avgsal 
          FROM emp 
          GROUP BY deptno) xb
WHERE  emp.deptno=xb.deptno
AND sal>avgsal

 

 

多列子查询：（多行，多列）

练习3 

 

• 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工 

SELECT job,mgr FROM emp
WHERE job IN(
SELECT job FROM emp WHERE deptno=10)
AND mgr IN(
SELECT mgr FROM emp WHERE deptno=10)
AND deptno<>10

 

或者

 

SELECT mgr,job FROM emp
WHERE (mgr,job) =ANY (
SELECT mgr,job FROM emp WHERE deptno=10)
AND deptno<>10

 

• 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工 

SELECT job,mgr FROM emp
WHERE (job IN(
SELECT job FROM emp WHERE deptno=10)
OR  mgr IN(
SELECT mgr FROM emp WHERE deptno=10))
AND deptno<>10

 

子查询中的空值 ：

查询不是经理的员工姓名。 

 

     SQL> SELECT      ename 

        2  FROM       emp 

        3  WHERE      empno NOT IN 

        4                              (SELECT mgr 

        5                               FROM    emp); 

     no rows selected. 

 

子查询返回的结果中含有空值 

    上面的SQL语句试图查找出没有下属的雇员，逻辑上，这个SQL语句应该会 

    返回8条记录，但是却一条也没返回，why? 

 

    因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这 

    是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值 

    有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符。 

改正：

SELECT ename FROM emp WHERE empno NOT IN(
SELECT   DISTINCT mgr FROM emp WHERE mgr IS NOT NULL) 

 

在 FROM 子句中使用子查询 

 

      • 查询比自己部门平均工资高的员工姓名，工资， 

         部门编号，部门平均工资 

 

      SQL> SELECT  a.ename, a.sal, a.deptno, b.salavg 

        2  FROM       emp a, (SELECT        deptno, avg(sal) salavg 

        3                       FROM        emp 

        4                       GROUP BY deptno) b 

        5  WHERE      a.deptno = b.deptno 

        6  AND        a.sal > b.salavg; 

 

      ENAME                SAL      DEPTNO        SALAVG 

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

      KING                 5000           10          2600 

      JONES                2975           20          2335 

      SCOTT                3000           20          2335 

      ... 

      6 rows selected. 

练习4 

 

• 1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资 

SELECT ename,e.job,dname ,avgsal
FROM emp e,dept d,(
                     SELECT  job,AVG(sal) avgsal
                     FROM emp
                     GROUP BY  job
                     )xb
WHERE e.deptno =d.deptno 
AND e.job= xb.job
AND e.sal>xb.avgsal 

• 2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。

SELECT e.ename,e.job

FROM emp e,

     (SELECT job,mgr FROM emp WHERE ename IN('SCOTT','BLAKE') ) xinbiao

WHERE e.job =xinbiao.job

    AND e.mgr =xinbiao.mgr

    AND e.ename NOT IN('SCOTT','BLAKE')

 

 

 或者

 SELECT e.ename,e.job

FROM emp e

     

WHERE (e.job,e.mgr) IN  (SELECT job,mgr FROM emp WHERE ename IN('SCOTT','BLAKE') ) 

    AND e.ename NOT IN('SCOTT','BLAKE')    

• 3.查询不是经理的员工姓名。

SELECT ename 

FROM emp

WHERE empno NOT IN (SELECT  DISTINCT mgr FROM emp WHERE mgr IS NOT NULL) 

 

 

 

ROWNUM 

 

• ROWNUM 

     –ROWNUM是一个伪列，伪列是使用上类似于表中的列，而 

        实际并没有存储在表中的特殊列； 

     –ROWNUM的功能是在每次查询时，返回结果集的顺序号， 

        这个顺序号是在记录输出时才一步一步产生的，第一行 

        显示为1，第二行为2，以此类推。 

 • ROWNUM 

      –ROWNUM使用的注意点： 

          • 1.如下SQL语句，SELECT * FROM EMP WHERE ROWNUM>2;查询 

            不到任何记录，因为ROWNUM是在记录输出时才生成，且总是 

            从1开始，所以输出的第一条记录不满足>2的条件，被过滤掉， 

            第二条的ROWNUM又成了1，又不满足〉2的条件，又被过滤掉， 

            依此类推，所以永远没有满足条件的记录，返回为空。所以 

            对于ROWNUM只能执行<、<=运算，不能执行>、>=或一个区间 

            运算Between..And等 

          •2.ROWNUM和ORDER BY一起使用时，因为ROWNUM在记录输出时 

            生成，而ORDER BY子句在最后执行，所以当两者一起使用时， 

            需要注意ROWNUM实际是已经被排了序的ROWNUM。 

TOP-N查询 

 

• TOP-N查询 

     –Top-N查询主要是实现表中按照某个列排序，输出最大或 

       最小的N条记录功能。 

         •Top-N分析语法： 

 

   SELECT [列名], ROWNUM 

   FROM      (SELECT [列名] 

               FROM 表名 

               ORDER  BY Top-N操作的列 ASC|DESC) 

   WHERE  ROWNUM <=  N; 

 

         •ASC:查询最小的N条记录 

         •DESC：查询最大的N条记录 

练习5 

• 1.查询入职日期最早的前5名员工姓名，入职日  期。 

 

SELECT ROWNUM,hiredate
FROM (SELECT ename,hiredate FROM emp
             ORDER BY hiredate ASC)
WHERE ROWNUM <=5
• 2.查询工作在CHICAGO并且入职日期最早的前2名 员工姓名，入职日期。 

SELECT ROWNUM ,hiredate
FROM (SELECT ename,hiredate
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND loc='CHICAGO'
ORDER BY hiredate ASC)
WHERE ROWNUM <=2

分页 

 • 分页查询 

      –在Oracle中，利用ROWNUM的特性，可以实现数据库端的分 

        页查询，查询语法为： 

      –1.当未指定需要按照某列排序，语法为： 

 

SELECT b.* 

FROM (SELECT ROWNUM rn,[             1,      2,....      n] 

                                 列名 列名               列名 

        FROM        1,[     2,...       n] 

               表名  表名              表名 

        WHERE [               AND ] ROWNUM <=               *            ) b 

                  条件表达式                            目标页数每页记录数 

WHERE rn > (目标页数-1)*每页记录数 

或 

SELECT b.* 

FROM (SELECT ROWNUM rn,[             1,      2,....      n] 

                                 列名 列名               列名 

        FROM        1,[     2,...       n] 

               表名  表名              表名 

         [WHERE 条件表达式]) b 

WHERE rn <=             *             and rn > (             -1)* 

               目标页数每页记录数                            目标页数          每页记录数 

思考：哪种方式效率高？ （第一种）

练习6 

 

• 1.按照每页显示5条记录，分别查询第1页，第2 页，第3页信息，要求显示员工姓名、入职日期 、部门名称。 

SELECT b.*
FROM (SELECT ROWNUM rn,ename,hiredate,dname
                    FROM emp e,dept d
                    WHERE e.deptno=d.deptno
                    AND
                    ROWNUM <=1*5)b
  WHERE rn>(1-1)*5

分页 

 • 分页查询 

      –2.当指定需要按照某列排序时，语法为： 

SELECT * 

FROM (SELECT ROWNUM  rn, b.* 

        FROM  (SELECT 列名1 [,列名2,....列名n] 

                  FROM       1,[     2,...      n] 

                        表名  表名              表名 

                  [WHERE 子句] 

                 ORDER BY 要排序的列 ASC|DESC ) b 

        WHERE ROWNUM <=              * 

                            目标页数每页记录数 

       ) 

WHERE rn > (目标页数-1)*每页记录数 ; 

或： 

SELECT * 

FROM (SELECT ROWNUM  rn, b.* 

        FROM  (SELECT 列名1 [,列名2,....列名n] 

                  FROM       1,[     2,...      n] 

                        表名  表名              表名 

                  [WHERE 子句] 

                 ORDER BY 要排序的列 ASC|DESC ) b 

        ) 

WHERE rn <=             *             and rn > (            -1)*             ; 

               目标页数每页记录数                           目标页数          每页记录数 

练习7 

• 1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名 、入职日期、部门名称、工资。 

第一页：

  SELECT *
  FROM
  (SELECT ROWNUM rn,b.*
  FROM (SELECT ename,hiredate,dname,sal
               FROM emp e,dept d
               WHERE e.deptno=d.deptno
               ORDER BY hiredate DESC) b
  WHERE ROWNUM <=1*5)
 WHERE rn >(1-1)*5

第二页：

  SELECT *
  FROM
  (SELECT ROWNUM rn,b.*
  FROM (SELECT ename,hiredate,dname,sal
               FROM emp e,dept d
               WHERE e.deptno=d.deptno
               ORDER BY hiredate DESC) b
  WHERE ROWNUM <=2*5)
 WHERE rn >(2-1)*5

第三页：

  SELECT *
  FROM
  (SELECT ROWNUM rn,b.*
  FROM (SELECT ename,hiredate,dname,sal
               FROM emp e,dept d
               WHERE e.deptno=d.deptno
               ORDER BY hiredate DESC) b
  WHERE ROWNUM <=3*5)
 WHERE rn >(3-1)*5