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;