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

2024-04-06 19:14:38

多列子查询

where条件中出现多列与子查询进行比较

多列子查询分为：成对比较和非成对比较

成对比较：

SQL> select ename,sal,job from emp where (deptno,job) in(select deptno,job from emp where ename='SCOTT');

ENAME    SAL JOB

------ ----- ---------

FORD     ANALYST

SCOTT    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    ANALYST

FORD     ANALYST

标量子查询表达式

标量子查询是从一行中返回一列值的子查询

标量子查询可用于:

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=) 
            then 'CANDA' else'USA' end ) location  
     from employees;

EMPLOYEE_ID LAST_NAME                 LOCAT

----------- ------------------------- -----

         OConnell                  USA

         Grant                     USA

         Whalen                    USA

         Hartstein                 CANDA

         Fay                       CANDA

         Mavris                    USA

         Baer                      USA

         Higgins                   USA

         Gietz                     USA

         King                      USA

         Kochhar                   USA

        .......................
107 rows selected.

ORDER BY子句中使用标量子查询

SQL> select ename,sal,deptno from emp order by (select dname from dept where emp.deptno=dept.deptno);

ENAME             SAL     DEPTNO

---------- ---------- ----------

CLARK

KING

MILLER

JONES

FORD

ADAMS

SMITH

SCOTT

WARD

TURNER

ALLEN

JAMES

BLAKE

MARTIN                    

 rows selected.

相互关联的子查询

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

---从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

JONES

BLAKE

SCOTT

KING

FORD

再举一个相互关联的例子

---查询至少换了两次工作的员工---
SQL> select employee_id,last_name,job_id from employees e where <=(select count(*) from job_history j where e.employee_id=j.employee_id);

EMPLOYEE_ID LAST_NAME                 JOB_ID

----------- ------------------------- ----------

         Whalen                    AD_ASST

         Kochhar                   AD_VP

         Taylor                    SA_REP

使用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

---------- ----------

       JONES

       BLAKE

       CLARK

       SCOTT

       KING

       FORD

 rows selected.

另一种写法：

SQL> select empno ,ename from emp  where empno in(select mgr from emp);

     EMPNO ENAME

---------- ----------

       JONES

       BLAKE

       CLARK

       SCOTT

       KING

       FORD

 rows selected.

我们来做另一个实验：查找不是领导的员工，也就是最基层的员工了。

SQL> select empno,ename from emp outer where not exists (select  from emp inner where outer.empno=inner.mgr);

     EMPNO ENAME

---------- ----------

       SMITH

       ALLEN

       WARD

       MARTIN

       TURNER

       ADAMS

       JAMES

       MILLER

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

关联UPDATE

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

SQL> create table e as select * from emp;

表已创建

SQL> alter table e add  dname varchar2();

表已更改。

SQL> desc e

 名称                                    是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER()

 ENAME                                              VARCHAR2()

 JOB                                                VARCHAR2()

 MGR                                                NUMBER()

 HIREDATE                                           DATE

 SAL                                                NUMBER(,)

 COMM                                               NUMBER(,)

 DEPTNO                                             NUMBER()

 DNAME                                              VARCHAR2()

SQL> select * from e;

EMPNO ENAME  JOB         MGR HIREDATE         SAL       COMM DEPTNO DNAME

----- ------ --------- ----- -------------- ----- ---------- ------ ----------

  SMITH  CLERK       -12月-

  ALLEN  SALESMAN    -2月 -

  WARD   SALESMAN    -2月 -

  JONES  MANAGER     -4月 -

  MARTIN SALESMAN    -9月 -

  BLAKE  MANAGER     -5月 -

  CLARK  MANAGER     -6月 -

  SCOTT  ANALYST     -4月 -

  KING   PRESIDENT       -11月-

  TURNER SALESMAN    -9月 -

  ADAMS  CLERK       -5月 -

  JAMES  CLERK       -12月-

  FORD   ANALYST     -12月-

  MILLER CLERK       -1月 -                      

已选择14行。

SQL> select * from dept;

DEPTNO DNAME          LOC

------ -------------- -------------

     ACCOUNTING     NEW YORK

     RESEARCH       DALLAS

     SALES          CHICAGO

     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

----- ------ --------- ----- -------------- ----- ---------- ------ ----------

  SMITH  CLERK       -12月-                        RESEARCH

  ALLEN  SALESMAN    -2月 -                    SALES

  WARD   SALESMAN    -2月 -                    SALES

  JONES  MANAGER     -4月 -                       RESEARCH

  MARTIN SALESMAN    -9月 -                   SALES

  BLAKE  MANAGER     -5月 -                       SALES

  CLARK  MANAGER     -6月 -                       ACCOUNTING

  SCOTT  ANALYST     -4月 -                       RESEARCH

  KING   PRESIDENT       -11月-                       ACCOUNTING

  TURNER SALESMAN    -9月 -                      SALES

  ADAMS  CLERK       -5月 -                       RESEARCH

  JAMES  CLERK       -12月-                        SALES

  FORD   ANALYST     -12月-                       RESEARCH

  MILLER CLERK       -1月 -                       ACCOUNTING

相互关联的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

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))

使用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

码农公寓

相关文章