oracle9i学习笔记之十六高级子查询

本文主要是介绍oracle9i学习笔记之十六高级子查询，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1.子查询回顾

SELECT   select_list
FROM     table
WHERE    expr operator(SELECT select_list
                         FROM    table);

例1：查询emp表，显示薪水大于平均薪水的雇员信息

     SELECT *
     FROM   emp
     WHERE sal>(SELECT AVG(sal)
                 FROM   emp);

例2：显示雇员的细节信息，要求这些雇员的经理和部门与雇员号为7521或7698是相同的，但不包括empno为7521或7698的雇员信息

   SELECT *
   FROM   emp
   WHERE (mgr,deptno) IN(SELECT mgr,deptno
                          FROM   emp
                          WHERE empno IN(7521,7698))
   AND    empno NOT IN(7521,7698);

例3：显示与薪水大于2900元的雇员相同部门、相同工作类型的其他雇员信息

     SELECT *
     FROM   emp
     WHERE (deptno,job) IN(SELECT deptno,job
                            FROM   emp
                            WHERE sal>2900)
     AND    sal<=2900;

例4：显示与薪水大于2900元的雇员相同部门、相同工作类型的其他雇员信息(在FROM子句中使用子查询)

    SELECT *
    FROM   emp e,(SELECT deptno,job,sal
                  FROM   emp
                  WHERE sal>2900) s
    WHERE e.deptno=s.deptno
    AND   e.job=s.job
    AND   e.sal!=s.sal;

2.相关子查询
1)相关子查询是一种读表中每一行并且依靠相关数据比较每行的值的方法，相关子查询被用于row-by-row处理。对外查询的每一行，每个子查询被执行一次。
2)嵌套子查询与相关子查询
(1)嵌套子查询的执行
     -内查询首先执行并且查找值
     -外查询用内查询的值执行一次
(2)相关子查询的执行
     -用外查询取得候选行
     -用候选行的值执行内查询
     -用来自内查询的值确认或取消候选行
     -重复直到无剩余的候选行

   SELECT column1,column2,...
   FROM   table1 outer
   WHERE column1 operator(SELECT column1,column2
                           FROM   table2
                           WHERE expr1=outer.expr2)

例：找出所有的雇员，他们挣的薪水高于该部门的平均薪水

    SELECT ename,deptno,sal
    FROM   emp outer
    WHERE sal>(SELECT AVG(sal)
                FROM   emp
                WHERE deptno=outer.deptno);
结果：
      ENAME DEPTNO SAL
      ALLEN    30    1600
      JONES    20    2975
      BLAKE    30    2850
      SCOTT    20    3000
      KING     10    5000
      FORD     20    3000
      小王     10    3000

3.EXISTS操作
EXISTS操作对在子查询的结果集中存在的行进行检验：
1)如果一个子查询行值被找到：
    -在内查询中的搜索不再继续
    -条件被标记为TRUE
2)如果一个子查询行值未找到
    -条件被标记为FALSE
    -在内查询中的搜索继续

例：查找至少有一个雇员的经理信息

    SELECT *
    FROM   emp outer
    WHERE EXISTS(SELECT 'X'
                  FROM   emp
                  WHERE mgr=outer.empno);
结果：
     EMPNO ENAME    JOB     MGR HIREDATE      SAL COMM DEPTNO
     7566   JONES MANAGER   7839 02-4月 -81 2975         20
     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 3000         20
     7839   KING   PRESIDENT       17-11月-81 5000         10
     7902   FORD   ANALYST   7566 03-12月-81 3000         20

4.NOT EXISTS操作
例：查找部门编号不在雇员表中的部门信息

   SELECT *
   FROM   dept
   WHERE NOT EXISTS(SELECT *
                     FROM   emp
                     WHERE emp.deptno=dept.deptno);

5.相关UPDATE
-用一个相关子查询来更新一个表中的行，该表中的行基于另一个表中的行

UPDATE table1 alias1
SET    column=(SELECT expression
                 FROM   table2 alias2
                 WHERE alias1.column=alias2.column);

例:1.在雇员表中增加部门名称列

     ALTER TABLE emp
     ADD   (dname VARCHAR2(14));

2.使用部门表中的部门名称更新雇员表中的部门名称

     UPDATE emp e
     SET    dname=(SELECT dname
                   FROM   dept d
                   WHERE e.deptno=d.deptno);

6.相关delete
-用一个相关子查询来删除一个表中的行，该表中的行基于另一个表中的行

DELETE FROM table1 alias1
WHERE column operator(SELECT expression
FROM table2 alias2
WHERE alias1.column=alias2.column);

例：用一个相关子查询删除emp表中的数据，被删除的数据是emp表和job_history表中有相同empno列值的数据

    DELETE FROM emp e
    WHERE empno IN (SELECT empno
                     FROM   job_history
                     WHERE empno=e.empno);

7.WITH子句
1)当一个查询块在一个复杂的查询中出现多次时，使用WITH子句，能够用在SELECT语句中使用相同查询块
2)WITH子句取回查询块的结果，并且将它存在用户的临时表空间中
3)WITH子句可以改善性能

例：用WITH子句，写一个查询来显示部门名称和该部门的合计薪水，那些人的合计薪水高于各部门的平均薪水

   WITH
   dept_costs AS(
       SELECT   d.dname,SUM(e.sal) AS dept_total
       FROM     emp e,dept d
       WHERE    e.deptno=d.deptno
       GROUP BY d.dname),
   avg_costs AS(
       SELECT SUM(dept_total)/COUNT(*) AS dept_avg
       FROM   dept_costs)
   SELECT *
   FROM   dept_costs
   WHERE dept_total>(SELECT dept_avg
                      FROM   avg_costs)
   ORDER BY dname;
结果：
      DNAME        DEPT_TOTAL
      ACCOUNTING   11750
      RESEARCH     14075

练习
1.显示变换过至少两次工作的雇员的详细情况（emp和job_history）

2.写一个子查询显示任何一个其部门号和薪水都与任何挣佣金的雇员的部门号和薪水相匹配的雇员的名字、部门号和薪水

SELECT ename,deptno,sal
FROM   emp
WHERE (deptno,sal) IN(SELECT deptno,sal
                         FROM   emp
                         WHERE comm IS NOT NULL);

3.显示任何其薪水和佣金与任何位于loc为CHICAGO的雇员薪水和佣金相匹配的雇员的名字、部门名和薪水

SELECT ename, dname, sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND (sal, NVL(comm,0)) IN (SELECT sal, NVL(comm,0)
        FROM emp e, dept d
        WHERE e.deptno = d.deptno
        AND d.loc = 'CHICAGO');

4.创建一个查询来显示所有其薪水和佣金与SCOTT相同的雇员的名字、受雇日期和薪水（不显示SCOTT的信息）

SELECT ename,hiredate,sal
FROM   emp
WHERE (sal,NVL(comm,0)) IN (SELECT sal,NVL(comm,0)
                                 FROM emp
                                WHERE ename='SCOTT')
AND   ename!='SCOTT';

5.创建一个查询来显示那些所挣薪水高于所有经理（job='MANAGER'）的雇员的名字、工作岗位和薪水。依据薪水从最高到最低排序结果集

SELECT ename,job,sal
FROM   emp
WHERE sal>ALL (SELECT sal
                  FROM   emp
                  WHERE job='MANAGER')
ORDER BY sal DESC;

6.显示那些住在城市名字以N开头的城市的雇员的ID、名字和部门号

SELECT empno,ename,deptno
FROM   emp
WHERE deptno IN(SELECT deptno
                   FROM   dept
                   WHERE loc LIKE 'N%');

7.写一个查询来查找所有其薪水多于他所在部门的平均薪水的雇员，显示名字、部门号和部门的平均薪水，按平均薪水排序

SELECT ename,e.deptno,sal,a.avg
FROM   emp e,(SELECT   deptno,AVG(sal) avg
                  FROM    emp
                 GROUP BY deptno) a
WHERE e.deptno=a.deptno
AND   e.sal>a.avg
ORDER BY a.avg;

8.写一个查询显示所挣薪水低于他们所在的部门平均薪水的雇员名

SELECT ename
FROM emp outer
WHERE sal< (SELECT AVG(sal)
      FROM emp inner
             WHERE deptno = outer.deptno);
或：
SELECT ename
         FROM emp outer
WHERE outer.sal< (SELECT AVG(inner.sal)
      FROM   emp inner
             WHERE inner.deptno = outer.deptno);

9.查找所有不是管理人员的雇员（雇员编号不在经理编号列的雇员）

SELECT *
FROM   emp
WHERE empno NOT IN(SELECT mgr
                      FROM   emp
                      WHERE mgr IS NOT NULL);
或：
SELECT *
FROM   emp outer
WHERE NOT EXISTS(SELECT 'X'
                    FROM   emp
                    WHERE mgr=outer.empno);
或：
SELECT *
FROM   emp
WHERE empno NOT IN (SELECT NVL(mgr,0)
                       FROM   emp);