溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Oracle專題8之Oracle的子查詢

發(fā)布時間：2020-07-23 10:12:44 來源：網(wǎng)絡(luò) 閱讀：1078 作者：糖醋白糖 欄目：關(guān)系型數(shù)據(jù)庫

1、Oracle的子查詢的概述

  • 什么是子查詢？
  • 子查詢是指嵌入在其他SQL語句中的SELECT語句，也稱之為嵌套查詢。
  • 可以使用子查詢的位置：where、select列表、having、from。

    a、在where子句中使用子查詢

  • 示例：查詢出和FORD是相同職位的員工：select * from emp where job = (select job from emp where ename='FORD'); 
    SQL> select * from emp where job = (select job from emp where ename='FORD');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7951 EASON      ANALYST         7566 01-12月-17           3000
                20
            7788 SCOTT      ANALYST         7566 19-4月 -87           3000
                20
            7902 FORD       ANALYST         7566 03-12月-81           3000
                20

    b、在select子句中使用子查詢

  • 示例：查詢出每個部門的編號、名稱、位置和部門人數(shù)。示例：select deptno, dname, loc, (select count(empno) from emp where emp.deptno = dept.deptno) count from dept; 
    SQL> select deptno, dname, loc, (select count(empno) from emp where emp.deptno = dept.deptno) count from dept;
DEPTNO DNAME          LOC                COUNT
    10 ACCOUNTING     NEW YORK               3
    20 RESEARCH       DALLAS                 6
    30 SALES          CHICAGO                6
    40 OPERATIONS     BOSTON                 0

    c、在having子句中使用子查詢

  • 舉例：查詢員工信息表，按照部門編號進(jìn)行分組，要求顯示員工的部門編號、平均工資，查詢條件時平均工資大于30號部門的最高工資。 
    SQL> select deptno, avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno = 30);
        DEPTNO   AVG(SAL)
                10 2916.66667

    d、在from子句中使用子查詢

  • 把子查詢的結(jié)果看成一張新的表。示例：查詢并顯示高于部門平均工資的雇員信息。 
    SQL> select ename, job, sal from emp, (select deptno, avg(sal) avgsal from emp group by deptno) dept where emp.deptno = dept.deptno and sal > avgsal;
ENAME      JOB              SAL
EASON      ANALYST         3000
ALLEN      SALESMAN        1600
JONES      MANAGER         2975
BLAKE      MANAGER         2850
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
FORD       ANALYST         3000
已選擇7行。

    2、Oracle的主查詢和子查詢

  • 什么是主查詢和子查詢？
    Oracle專題8之Oracle的子查詢

    a、一個主查詢可以有多個子查詢

  • 舉例：顯示職位和7521的職位相同并工資大于7934這個員工工資的員工信息。 
    SQL> select * from emp where job = (select job from emp where empno = 7521) and sal > (select sal from emp where empno = 7934);
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300
                30
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30

    b、子查詢的執(zhí)行順序

  • 一般先執(zhí)行子查詢，再執(zhí)行主查詢，但相關(guān)子查詢例外。
  • 舉例：查詢員工表中小于平均工資的員工信息。 
    SQL> select empno, ename, sal from emp where sal < (select avg(sal) from emp);
         EMPNO ENAME             SAL
            7369 G_EASON           800
            7499 ALLEN            1600
            7521 WARD             1250
            7654 MARTIN           1250
            7844 TURNER           1500
            7876 ADAMS            1100
            7900 JAMES             950
            7934 MILLER           1300
已選擇8行。

    c、相關(guān)子查詢

  • 當(dāng)子查詢需要引用主查詢的表列時，Oracle會執(zhí)行相關(guān)查詢。
  • 相關(guān)子查詢是先執(zhí)行主查詢，在執(zhí)行子查詢。
  • 示例：查詢工資高于部門平均工資的雇員名、工資和部門號： 
    SQL> select ename, sal, deptno from emp e where sal > (select avg(sal) from emp where deptno = e.deptno);
ENAME             SAL     DEPTNO
EASON            3000         20
ALLEN            1600         30
JONES            2975         20
BLAKE            2850         30
SCOTT            3000         20
KING             5000         10
FORD             3000         20
已選擇7行。

    d、主查詢和子查詢可以不是同一張表

  • 主查詢和子查詢可以查詢的不是同一張表。
  • 示例：查詢部門名稱時ACCOUNTING的員工信息。 
    SQL> select * from emp where deptno = (select deptno from dept where dname='ACCOUNTING');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7782 CLARK      MANAGER         7839 09-6月 -81           2450
                10
            7839 KING       PRESIDENT            17-11月-81           5000
                10
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10

    3、Oracle的子查詢：單行子查詢

  • 子查詢的類型：單行子查詢和多行子查詢。
  • 單行子查詢：只返回一行數(shù)據(jù)的子查詢語句。
  • 使用單行比較操作符：
操作符 含義
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于

a、使用單行子查詢

  • 示例1：顯示與JAMES同部門的所有其他的員工姓名、工資、部門號。 
    SQL> select ename, sal, deptno from emp where deptno = (select deptno from emp where ename='JAMES') AND ename <> 'JAMES';
ENAME             SAL     DEPTNO-
ALLEN            1600         30
WARD             1250         30
MARTIN           1250         30
BLAKE            2850         30
  • 示例2：查詢大于等于公司平均工資的員工的姓名、職位、工資。 
    SQL> select ename, job, sal from emp where sal >= (select avg(sal) from emp);
ENAME      JOB              SAL
EASON      ANALYST         3000
JONES      MANAGER         2975
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
FORD       ANALYST         3000
已選擇7行。
  • 示例3：查詢部門名稱不是‘SAVE’的員工。 
    SQL> select * from emp where deptno <> (select deptno from dept where dname= 'SALES');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7951 EASON      ANALYST         7566 01-12月-17           3000
                20
          ......
            7902 FORD       ANALYST         7566 03-12月-81           3000
                20
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
已選擇9行。

    b、非法使用單行子查詢

  • 示例：select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno); 
    SQL> select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno);
select ename, job, sal from emp where sal = (select max(sal) from emp group by deptno)
第 1 行出現(xiàn)錯誤:
ORA-01427: 單行子查詢返回多個行
  • select max(sal) from emp group by deptno;sql語句返回多行數(shù)據(jù)。 
    SQL> select max(sal) from emp group by deptno;
MAX(SAL)
  2850
  3000
  5000

    4、Oracle的子查詢：多行子查詢

  • 多行子查詢是指返回多行數(shù)據(jù)的子查詢語句。
  • 使用多行比較操作符：（使用多行子查詢時必須使用多行比較操作符。）
運算符 含義
IN 等于列表中的任何一個
ALL 和子查詢返回的所有值進(jìn)行比較
ANY 和子查詢返回的任一值進(jìn)行比較

a、在多行子查詢中使用IN操作符

  • 示例：查詢工作地點在NEW YORK和CHICAGO的部門所對應(yīng)的員工信息。 
    SQL> select * from emp where deptno in (select deptno from dept where loc='NEW YORK' or loc = 'CHICAGO');
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
            ......
            7698 BLAKE      MANAGER         7839 01-5月 -81           2850
                30
            7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400
                30
已選擇9行。

    b、在多行子查詢中使用ALL操作符

  • 示例：查詢高于30號部門所有員工工資的員工名、工資和部門號。 
    SQL> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno = 30);
ENAME             SAL     DEPTNO
JONES            2975         20
EASON            3000         20
FORD             3000         20
SCOTT            3000         20
KING             5000         10

    c、在多行子查詢中使用ANY操作符

  • 示例：查詢高于10號部門任意一個員工工資的員工名、工資和部門號。 
    SQL> select ename, sal, deptno from emp where sal > any (select sal from emp where deptno = 10);
ENAME             SAL     DEPTNO
KING             5000         10
EASON            3000         20
......
ALLEN            1600         30
TURNER           1500         30
已選擇9行。

    5、Oracle的子查詢需要注意的問題

    a、不可以在group by子句中使用子查詢。

  • 示例：select avg(sal) from emp group by (select deptno from emp); 
    SQL> select avg(sal) from emp group by (select deptno from emp);
select avg(sal) from emp group by (select deptno from emp)
第 1 行出現(xiàn)錯誤:
ORA-22818: 這里不允許出現(xiàn)子查詢表達(dá)式

    b、在TOP-N分析問題中，需對子查詢排序

  • 示例：顯示員工信息表中工資最高的前五名員工。 
    SQL> select rownum, empno, ename, sal from (select * from emp order by sal desc) where rownum <= 5;
        ROWNUM      EMPNO ENAME             SAL
                 1       7839 KING             5000
                 2       7951 EASON            3000
                 3       7902 FORD             3000
                 4       7788 SCOTT            3000
                 5       7566 JONES            2975

    c、單行子查詢的空值問題

  • 示例：select ename, job, from emp where job = (select job from emp wehre ename = 'Ruby'); 
    SQL> select ename, job from emp where job = (select job from emp where ename = 'Ruby');
未選定行
  • 如果子查詢返回到的是一個空值，那么主查詢將不會查詢到任何結(jié)果。

    d、多行子查詢的空值問題

  • 示例：select * from emp where empno not in (select mgr from emp); 
    SQL> select mgr from emp;
             MGR
            7566
            null
            ......
            7839
            7566
            7698
            已選擇15行。
 SQL> select * from emp where empno not in (select mgr from emp);
未選定行
  • 多行子查詢中如果出現(xiàn)空值，則主查詢將不會查詢到任何結(jié)果。 
    SQL> select * from emp where empno not in (select mgr from emp where mgr is not null);
         EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
        DEPTNO
            7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
                30
            7951 EASON      ANALYST         7566 01-12月-17           3000
                20
         ......
            7934 MILLER     CLERK           7782 23-1月 -82           1300
                10
            7900 JAMES      CLERK           7698 03-12月-81            950
                30
已選擇9行。
向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手