進階子查詢【weber出品必屬精品】

標籤：style   blog   color   使用   io   for   ar   art   

1. 多列子查詢

   where條件中出現多列與子查詢進行比較

   多列子查詢分為：成對比較和非成對比較

   成對比較：

   SQL> select ename,sal,job from emp where (deptno,job) in(select deptno,job from emp where ename=‘SCOTT‘);ENAME    SAL JOB------ ----- ---------FORD    3000 ANALYSTSCOTT   4000 ANALYST

   非成對比較：

   select ename,sal,job from emp where deptno=(select deptno from emp where ename=‘SCOTT‘)and   job = (select job from emp where ename=‘SCOTT‘)ENAME    SAL JOB------ ----- ---------SCOTT   4000 ANALYSTFORD    3000 ANALYST

2.  標量子查詢運算式

   標量子查詢是從一行中返回一列值的子查詢

   標量子查詢可用於:

   1. DECODE 和CASE的條件和運算式部分

   2. 除了GROUP BY之外的所有SELECT語句

   CASE運算式中使用標量子查詢：CASE運算式可以實現if --then-- else的功能

   SQL> select employee_id,last_name,(
   　　    case 
               when department_id=(select department_id from departments where location_id=1800) 
               then ‘CANDA‘ else‘USA‘ end ) location  
        from employees;EMPLOYEE_ID LAST_NAME                 LOCAT----------- ------------------------- -----        198 OConnell                  USA        199 Grant                     USA        200 Whalen                    USA        201 Hartstein                 CANDA        202 Fay                       CANDA        203 Mavris                    USA        204 Baer                      USA        205 Higgins                   USA        206 Gietz                     USA        100 King                      USA        101 Kochhar                   USA        .......................
   107 rows selected.

3.  ORDER BY子句中使用標量子查詢

   SQL> select ename,sal,deptno from emp order by (select dname from dept where emp.deptno=dept.deptno);ENAME             SAL     DEPTNO---------- ---------- ----------CLARK            2450         10KING             5000         10MILLER           1300         10JONES            2975         20FORD             3000         20ADAMS            1100         20SMITH             800         20SCOTT            3000         20WARD             1250         30TURNER           1500         30ALLEN            1600         30JAMES             950         30BLAKE            2850         30MARTIN           1250         3014 rows selected.

4.  相互關聯的子查詢

   相互關聯子查詢用於行-執行-行的處理主查詢的每一行子查詢都要執行一次：通俗的說就是，主查詢--->子查詢--->主查詢。就是這個執行順序。

   ---從emp表中查詢 薪水大於 部門平均薪水 的使用者名稱，部門編號和薪水，沒有大於的則不會被顯示出來。
   select ename,deptno,salfrom emp outerwhere sal>(select avg(sal) from emp inner where outer.deptno=inner.deptno );ENAME  DEPTNO   SAL------ ------ -----ALLEN      30  1600JONES      20  2975BLAKE      30  2850SCOTT      20  4000KING       10  5000FORD       20  3000

5.  再舉一個相互關聯的例子

   ---查詢至少換了兩次工作的員工---
   SQL> select employee_id,last_name,job_id from employees e where 2<=(select count(*) from job_history j where e.employee_id=j.employee_id);EMPLOYEE_ID LAST_NAME                 JOB_ID----------- ------------------------- ----------        200 Whalen                    AD_ASST        101 Kochhar                   AD_VP        176 Taylor                    SA_REP

6.  使用EXISTS

   EXISTS 檢測子查詢中行的存在性

   1.如果值存在於子查詢中:
      終止在子查詢中的尋找
      條件返回TRUE

   2.如果值不存在於子查詢中:
      條件返回FALSE
      繼續在子查詢中的尋找

   ---樣本：尋找是領導的員工
   SQL> select empno,ename from emp outer where  exists(select mgr from emp inner where outer.empno=inner.mgr);     EMPNO ENAME---------- ----------      7566 JONES      7698 BLAKE      7782 CLARK      7788 SCOTT      7839 KING      7902 FORD6 rows selected.

   另一種寫法：

   SQL> select empno ,ename from emp  where empno in(select mgr from emp);     EMPNO ENAME---------- ----------      7566 JONES      7698 BLAKE      7782 CLARK      7788 SCOTT      7839 KING      7902 FORD6 rows selected.

7. 我們來做另一個實驗：尋找不是領導的員工，也就是最基層的員工了。

   SQL> select empno,ename from emp outer where not exists (select 1 from emp inner where outer.empno=inner.mgr);     EMPNO ENAME---------- ----------      7369 SMITH      7499 ALLEN      7521 WARD      7654 MARTIN      7844 TURNER      7876 ADAMS      7900 JAMES      7934 MILLER8 rows selected.
   ---這裡解釋一下：not exists裡的整數1的作用，只要是個整數即可，‘abc’字串也可以。

    

8.  
9.  
10.  
11.