【课堂笔记】【SQL】17.Retrieving Data Using Subqueries - 重庆思庄Oracle、Redhat认证学习论坛

SQL> select employee_id,last_name from employees e
2 order by (select department_name from departments d
3 where e.department_id=d.department_id);

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        205 Higgins
        206 Gietz
        200 Whalen
        100 King
        101 Kochhar
        102 De Haan
        112 Urman
        108 Greenberg
        109 Faviet
        110 Chen
        111 Sciarra

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        113 Popp
        203 Mavris
        107 Lorentz
        105 Austin
        103 Hunold

2YC2RMdR.jpg (30.4 KB, 下载次数: 140)

下载附件

【课堂笔记】【SQL】17.Retrieving Data Using Subqueries

2012-11-5 17:02 上传

示例：

SQL> select last_name,salary,department_id from employees outer
2 where salary>(select avg(salary)
3 from employees where department_id=outer.department_id);

LAST_NAME                     SALARY DEPARTMENT_ID
------------------------- ---------- -------------
Hartstein                      13000            20
Higgins                        12008           110
King                           24000            90
Hunold                          9000            60
Ernst                           6000            60
Greenberg                      12008           100
Faviet                          9000           100
Raphaely                       11000            30
Weiss                           8000            50
Fripp                           8200            50
Kaufling                        7900            50

LAST_NAME SALARY DEPARTMENT_ID
------------------------- ---------- -------------
Vollman 6500 50

作者: 5dm 时间: 2012-11-5 17:21

续：

EXISTS存在：功能有点类似IN。 NOT EXISTS就是不存在。

如果找到一条匹配的记录，则不再继续往子查询的查找。与IN比起来还是有很大优点的。

示例：

SELECT employee_id, last_name, job_id, department_id

FROM employees outer

WHERE EXISTS ( SELECT 'X'

FROM employees

WHERE manager_id =

outer.employee_id);

关联更新：

示例：

UPDATE empl6 e

SET department_name =

(SELECT department_name

FROM departments d

WHERE e.department_id = d.department_id);

关联删除：

示例：

DELETE FROM empl6 E

WHERE employee_id =

           (SELECT employee_id

            FROM   emp_history

            WHERE employee_id = E.employee_id);

WITH，将同样的查询块定义起来将其结果放在临时表空间中，这样重复使用可以提供效率。注意，必须先定义再使用。

示例：

SQL> with
2 dept_costs as
3 (select d.department_name,sum(e.salary) as dept_total
4 from employees e join departments d
5 on e.department_id=d.department_id
6 group by d.department_name),
7 avg_cost as
8 (select sum(dept_total)/count(*) as dept_avg
9 from dept_costs)
10 select * from dept_costs where dept_total>(select dept_avg
11 from avg_cost)
12 order by department_name;

DEPARTMENT_NAME                DEPT_TOTAL
------------------------------ ----------
Sales                              304500
Shipping                           156400

作者: 张清扬 时间: 2013-5-6 11:55

另外一个说法是exists操作符只要子查询返回结果不论这个结果是null 还是其他值立刻返回。

同样 in 无论是否已经找到结果会遍历子查询的结果集

实际工作中，exists和in都可以使用情况下，针对oltp，前者和后则的表现查询非常巨大。

子查询返回结果集越大，in就性能就越差。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)