oracle学习笔记之六 子查询

 1.SELECT select_list
  FROM   table
  WHERE  expr operater 
         (SELECT select_list
          FROM   table);
 
  1)Expr operater包括比较运算符：
      单行运算符（=、>、>=、<、<=、<>）
      多行运算符（IN、ANY、ALL）
  2)子查询可以嵌于一下SQL子句中：
    WHERE 子句
    HAVING 子句
    FROM 子句
  例：查询出比雇员为SCOTT工资高的其他雇员
      SELECT ename
      FROM   emp
      WHERE  sal>
             (SELECT sal
              FROM   emp
              WHERE  ename='SCOTT');
  结果：
        ENAME 
        KING 

  

  注：1)子查询要用括号括起来
      2)将子查询放在比较运算符的右边
      3)在子查询中，一般不需要ORDER BY子句
      4)对于单行子查询要用单行运算符
      5)对于多行子查询要用多行运算符

2.子查询的类型：单行子查询；多行子查询；多列子查询

3.单行子查询
1)子查询只返回一行,主查询使用单行运算符(=，>,>=,<,<=,<>)

  例1：显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作
       SELECT ename,job
       FROM   emp
       WHERE  job=
              (SELECT job
               FROM   emp
               WHERE  empno=7369)
       AND    sal>
              (SELECT sal
               FROM   emp
               WHERE  empno=7876);

  结果：
        ENAME      JOB 
        MILLER     CLERK
 
2)子查询中可以使用组函数 
 
  例2：显示工资最低的雇员的姓名、工作和工资
       SELECT ename,job,sal
       FROM   emp
       WHERE  sal=
              (SELECT MIN(sal)
               FROM   emp);
  结果：
        ENAME     JOB      SAL 
        SMITH     CLERK    800 

3)HAVING子句中的子查询
  -Oracle服务器先执行子查询
  -Oracle服务器将结果返回给主查询的HAVING子句
 
  例3：SELECT   deptno,MIN(sal)
       FROM     emp
       GROUP BY deptno
       HAVING   MIN(sal)>
                (SELECT MIN(sal)
                 FROM   emp
                 WHERE  deptno=20);
  结果：
       DEPTNO   MIN(SAL) 
           10       1300 
           30        950 

4)判断语句
  例1：SELECT ename,job
       FROM   emp
       WHERE  job=
              (SELECT job
               FROM   emp
               WHERE  ename='SCOTT');
  结果：
       ENAME      JOB 
       SCOTT      ANALYST 
       FORD       ANALYST 

  例2：SELECT empno,ename
        FROM   emp
        WHERE  sal=
               (SELECT   MIN(sal)         
                FROM     emp
                GROUP BY deptno);
 
  结果：
          (SELECT   MIN(sal)
             *
       ERROR 位于第 4 行:
       ORA-01427: 单行子查询返回多于一个行

4.多行子查询
1)返回多余一行记录
2)主查询使用多行比较运算符
  IN----等于列表中的任意一项
  ALL---和内部查询返回的全部结果比较(>ALL:比最大的大;<ALL:比最小的小)
  ANY---和内部查询返回的结果逐个比较(>ANY:比最大值小;<ANY:比最小值大;=ANY等效于IN)
 
  例1：
       SELECT empno,ename,job
       FROM   emp
       WHERE  sal>ALL
              (SELECT   AVG(sal)         
               FROM     emp
               GROUP BY deptno);
  结果：
       EMPNO    ENAME    JOB 
        7566    JONES    MANAGER 
        7788    SCOTT    ANALYST 
        7839    KING     PRESIDENT 
        7902    FORD     ANALYST 
 
  例2：
      SELECT empno,ename,job
      FROM   emp
      WHERE  sal>ANY
             (SELECT sal         
              FROM   emp
              WHERE  job='CLERK')
      AND    job<>'CLERK';
      
  结果：
        EMPNO    ENAME    JOB 
         7499    ALLEN    SALESMAN 
         7521    WARD     SALESMAN 
         7566    JONES    MANAGER 

5.多列子查询
  -主查询和来自一个多行多列子查询的返回值进行比较
 
  例：从emp表中找出与部门标号为30的任意一个雇员的薪水和佣金完全相同的雇员，并显示其姓名、部门编号、薪水和佣金。
  方法一：SELECT ename,deptno,sal,comm
          FROM   emp
          WHERE  (sal,NVL(comm,0)) IN
                 (SELECT sal,NVL(comm,0)         
                  FROM   emp
                  WHERE  deptno=30)
          AND    deptno<>30;
 
  方法二：SELECT ename,deptno,sal,comm
          FROM   emp
          WHERE  sal IN(SELECT sal
                        FROM   emp
                        WHERE  deptno=30)
          AND    NVL(comm,-1) IN (SELECT NVL(comm,-1)
                                  FROM   emp
                                  WHERE  deptno=30)
          AND    deptno<>30;
 
6.在FROM子句中使用子查询
  例：
     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;

  结果：
       ENAME   SAL    DEPTNO   SALAVG 
       KING    5000     10     2916.66667 
       FORD    3000     20     2175 
       SCOTT   3000     20     2175 
       JONES   2975     20     2175 
       ALLEN   1600     30     1566.66667 
       BLAKE   2850     30     1566.66667 

练习
1.查询emp表，显示与雇员SCOTT在同一个部门工作的所有雇员的ename和hiredate

   SELECT   ename,hiredate
   FROM     emp
   WHERE    job=
                (SELECT job
                 FROM   emp
                 WHERE  ename='SCOTT');

2.查询emp表，显示所有雇员的ename、job、sal和hiredate信息

   SELECT   ename,job,sal,hiredate
   FROM     emp
   WHERE    sal>
                (SELECT   AVG(sal)
                 FROM     emp
                 WHERE    ename='SCOTT');

3.查询有下属的雇员信息

   SELECT   *
   FROM     emp
   WHERE    empno IN
                   (SELECT   mgr
                    FROM     emp);

4.查询薪水大于工作岗位是SALESMAN的所有部门平均薪水的雇员编号、雇员名、工作岗位、薪水

   SELECT   empno,ename,job,sal
   FROM     emp
   WHERE    sal>ALL
                  (SELECT   AVG(sal)
                   FROM     emp
                   WHERE    job='SALESMAN'
                   GROUP BY deptno);

5.根据部门和工作岗位分组，显示最大的薪水合计数以及它们对应的部门及岗位

    SELECT   deptno,job,t.sum_t
    FROM     emp e,(SELECT MAX(total) sum_t
                   FROM   (SELECT   SUM(sal) total
                           FROM     emp
                           GROUP BY deptno,job)
                    )t
   GROUP BY deptno,job, t.sum_t
   HAVING   SUM(sal)=t.sum_t;