高级子查询【weber出品必属精品】

  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 ANALYST
SCOTT   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 ANALYST
FORD    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         10
KING             5000         10
MILLER           1300         10
JONES            2975         20
FORD             3000         20
ADAMS            1100         20
SMITH             800         20
SCOTT            3000         20
WARD             1250         30
TURNER           1500         30
ALLEN            1600         30
JAMES             950         30
BLAKE            2850         30
MARTIN           1250         30

14 rows selected.
  4.  相互关联的子查询

    相互关联子查询用于行-执行-行的处理主查询的每一行子查询都要执行一次：通俗的说就是，主查询--->子查询--->主查询。就是这个执行顺序。

    ---从emp表中查询 薪水大于 部门平均薪水 的用户名，部门编号和薪水，没有大于的则不会被显示出来。
    select     ename,deptno,sal
from emp outer
where sal>(select avg(sal) from emp inner where outer.deptno=inner.deptno );

ENAME  DEPTNO   SAL
------ ------ -----
ALLEN      30  1600
JONES      20  2975
BLAKE      30  2850
SCOTT      20  4000
KING       10  5000
FORD       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 FORD

6 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 FORD

6 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 MILLER

8 rows selected.
    ---这里解释一下：not exists里的整数1的作用，只要是个整数即可，‘abc’字符串也可以。
  8.  关联UPDATE

    使用相互关联的子查询更新一个表的行基于另外一个表的行

    SQL> create table e as select * from emp;

表已创建

SQL> alter table e add  dname varchar2(10);

表已更改。

SQL> desc e
 名称                                    是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(10)

SQL> select * from e;

EMPNO ENAME  JOB         MGR HIREDATE         SAL       COMM DEPTNO DNAME
----- ------ --------- ----- -------------- ----- ---------- ------ ----------
 7369 SMITH  CLERK      7902 17-12月-80       800                20
 7499 ALLEN  SALESMAN   7698 20-2月 -81      1600        300     30
 7521 WARD   SALESMAN   7698 22-2月 -81      1250        500     30
 7566 JONES  MANAGER    7839 02-4月 -81      2975                20
 7654 MARTIN SALESMAN   7698 28-9月 -81      1250       1400     30
 7698 BLAKE  MANAGER    7839 01-5月 -81      2850                30
 7782 CLARK  MANAGER    7839 09-6月 -81      2450                10
 7788 SCOTT  ANALYST    7566 19-4月 -87      4000                20
 7839 KING   PRESIDENT       17-11月-81      5000                10
 7844 TURNER SALESMAN   7698 08-9月 -81      1500          0     30
 7876 ADAMS  CLERK      7788 23-5月 -87      1100                20
 7900 JAMES  CLERK      7698 03-12月-81       950                30
 7902 FORD   ANALYST    7566 03-12月-81      3000                20
 7934 MILLER CLERK      7782 23-1月 -82      1300                10

已选择14行。

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON


SQL> update e set dname=(select dname from dept d where  e.deptno=d.deptno);

已更新14行。

SQL> commit;

提交完成。

SQL> select * from e;

EMPNO ENAME  JOB         MGR HIREDATE         SAL       COMM DEPTNO DNAME
----- ------ --------- ----- -------------- ----- ---------- ------ ----------
 7369 SMITH  CLERK      7902 17-12月-80       800                20 RESEARCH
 7499 ALLEN  SALESMAN   7698 20-2月 -81      1600        300     30 SALES
 7521 WARD   SALESMAN   7698 22-2月 -81      1250        500     30 SALES
 7566 JONES  MANAGER    7839 02-4月 -81      2975                20 RESEARCH
 7654 MARTIN SALESMAN   7698 28-9月 -81      1250       1400     30 SALES
 7698 BLAKE  MANAGER    7839 01-5月 -81      2850                30 SALES
 7782 CLARK  MANAGER    7839 09-6月 -81      2450                10 ACCOUNTING
 7788 SCOTT  ANALYST    7566 19-4月 -87      4000                20 RESEARCH
 7839 KING   PRESIDENT       17-11月-81      5000                10 ACCOUNTING
 7844 TURNER SALESMAN   7698 08-9月 -81      1500          0     30 SALES
 7876 ADAMS  CLERK      7788 23-5月 -87      1100                20 RESEARCH
 7900 JAMES  CLERK      7698 03-12月-81       950                30 SALES
 7902 FORD   ANALYST    7566 03-12月-81      3000                20 RESEARCH
 7934 MILLER CLERK      7782 23-1月 -82      1300                10 ACCOUNTING
  9.  相互关联的DELETE

    使用相互关联的DELETE删除一个表的行基于另外一个表的行

    SQL> conn hr/hr
已连接。

SQL> create table emp as select * from employees;

表已创建。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
REGIONS                        TABLE
COUNTRIES                      TABLE
LOCATIONS                      TABLE
DEPARTMENTS                    TABLE
JOBS                           TABLE
EMPLOYEES                      TABLE
JOB_HISTORY                    TABLE
EMP                            TABLE
EMP_DETAILS_VIEW               VIEW

已选择9行。


SQL> delete emp where exists(select employee_id from job_history where emp.employee_id=employee_id);

已删除7行。

SQL> roll
  10.  WITH子句

    使用WITH子句, 在复杂查询中如果相同的查询块多次出现，可以重复使用

    WITH子句将结果存放到用户的临时表空间中

    WITH子句提高了性能

    select dname,sum(sal) from emp e,dept d where e.deptno=d.deptno
group by dname
having sum(sal) >(select sum(total_sal)/count(*)from (select dname,sum(sal) total_sal from emp e,dept d where e.deptno=d.deptno group by dname))
  11.  使用with子句 
    with
dept_costs as(select dname,sum(sal) total_sal from emp e,dept d where e.deptno=d.deptno
group by dname),
avg_costs as (select sum(total_sal)/count(*) avg_sal from dept_costs)
select * from dept_costs where total_sal>(select avg_sal from avg_costs)
order by dname
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yaoweber/p/3921951.html