[Oracle数据库学习]十八、高级子查询

D12

子查询

子查询是嵌套在SQL语句中的另一个SELECT语句。

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

子查询在主查询之前进行，结果共主查询使用。

示例：

SELECT last_name 
FROM hr.employees
WHERE salary > (SELECT salary
                FROM hr.employees
                WHERE employee_id = 149) ;
LAST_NAME
King
Kochhar
De Haan
Greenberg
Raphaely
Russell
Partners
Errazuriz
Cambrault
Ozer
Abel
Hartstein
Higgins

13 rows selected.

 

多列子查询

子查询返回多列，主查询与子查询返回的多个列进行比较。

多列子查询中的比较分为两种:
1）成对比较
2）不成对比较

 

成对比较

SELECT employee_id, manager_id, department_id
FROM hr.employees
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id
                                      FROM hr.employees
                                      WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);
EMPLOYEE_IDMANAGER_IDDEPARTMENT_ID
175 149 80
176 149 80
177 149 80
179 149 80

4 rows selected.

 

不成对比较

SELECT employee_id, manager_id, department_id
FROM hr.employees
WHERE manager_id IN (SELECT manager_id
                     FROM hr.employees
                     WHERE employee_id IN (174,141))
AND department_id IN (SELECT department_id
                     FROM hr.employees
                     WHERE employee_id IN (174,141))
AND employee_id NOT IN (174,141);
EMPLOYEE_IDMANAGER_IDDEPARTMENT_ID
142 124 50
143 124 50
144 124 50
196 124 50
197 124 50
198 124 50
199 124 50
175 149 80
176 149 80
177 149 80
179 149 80

11 rows selected.

 

在FROM子句中使用子查询

SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM hr.employees a, (SELECT department_id, AVG(salary) salavg
                      FROM hr.employees
                      GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
LAST_NAMESALARYDEPARTMENT_IDSALAVG
King 24000 90 19333.3333333333333333333333333333333333
Hunold 9000 60 5760
Ernst 6000 60 5760
Greenberg 12008 100 8601.333333333333333333333333333333333333
Faviet 9000 100 8601.333333333333333333333333333333333333
Raphaely 11000 30 4150
Weiss 8000 50 3475.555555555555555555555555555555555556
Fripp 8200 50 3475.555555555555555555555555555555555556
Kaufling 7900 50 3475.555555555555555555555555555555555556
Vollman 6500 50 3475.555555555555555555555555555555555556
Mourgos 5800 50 3475.555555555555555555555555555555555556
Ladwig 3600 50 3475.555555555555555555555555555555555556
Rajs 3500 50 3475.555555555555555555555555555555555556
Russell 14000 80 8955.882352941176470588235294117647058824
Partners 13500 80 8955.882352941176470588235294117647058824
Errazuriz 12000 80 8955.882352941176470588235294117647058824
Cambrault 11000 80 8955.882352941176470588235294117647058824
Zlotkey 10500 80 8955.882352941176470588235294117647058824
Tucker 10000 80 8955.882352941176470588235294117647058824
Bernstein 9500 80 8955.882352941176470588235294117647058824
Hall 9000 80 8955.882352941176470588235294117647058824
King 10000 80 8955.882352941176470588235294117647058824
Sully 9500 80 8955.882352941176470588235294117647058824
McEwen 9000 80 8955.882352941176470588235294117647058824
Vishney 10500 80 8955.882352941176470588235294117647058824
Greene 9500 80 8955.882352941176470588235294117647058824
Ozer 11500 80 8955.882352941176470588235294117647058824
Bloom 10000 80 8955.882352941176470588235294117647058824
Fox 9600 80 8955.882352941176470588235294117647058824
Abel 11000 80 8955.882352941176470588235294117647058824
Sarchand 4200 50 3475.555555555555555555555555555555555556
Bull 4100 50 3475.555555555555555555555555555555555556
Chung 3800 50 3475.555555555555555555555555555555555556
Dilly 3600 50 3475.555555555555555555555555555555555556
Bell 4000 50 3475.555555555555555555555555555555555556
Everett 3900 50 3475.555555555555555555555555555555555556
Hartstein 13000 20 9500
Higgins 12008 110 10154

38 rows selected.

 

单列子查询表达式

单列子查询表达式是在一行中只返回一列的子查询。

 

Oracle8i只在下列情况下可以使用, 例如:
–SELECT语句(FROM和WHERE子句)
–INSERT语句中的VALUES列表中


Oracle9i中单列子查询表达式可在下列情况下使用:
–DECODE和CASE
–SELECT中除GROUP BY子句以外的所有子句中

 

在CASE语句中使用子查询

先看一下子查询的结果是单行单列：

SELECT department_id 
FROM hr.departments
WHERE location_id = 1500;
DEPARTMENT_ID
50

示例：

SELECT employee_id, last_name,
    (CASE 
     WHEN department_id = (SELECT department_id 
                           FROM hr.departments
                           WHERE location_id = 1500) 
     THEN 'Canada' ELSE 'USA' END) location
FROM hr.employees;
EMPLOYEE_IDLAST_NAMELOCATION
100 King USA
101 Kochhar USA
102 De Haan USA
103 Hunold USA
104 Ernst USA
105 Austin USA
106 Pataballa USA
107 Lorentz USA
108 Greenberg USA
109 Faviet USA
110 Chen USA
111 Sciarra USA
112 Urman USA
113 Popp USA
114 Raphaely USA
115 Khoo USA
116 Baida USA
117 Tobias USA
118 Himuro USA
119 Colmenares USA
120 Weiss Canada
121 Fripp Canada
122 Kaufling Canada
123 Vollman Canada
124 Mourgos Canada
125 Nayer Canada
126 Mikkilineni Canada
127 Landry Canada
128 Markle Canada
129 Bissot Canada
130 Atkinson Canada
131 Marlow Canada
132 Olson Canada
133 Mallin Canada
134 Rogers Canada
135 Gee Canada
136 Philtanker Canada
137 Ladwig Canada
138 Stiles Canada
139 Seo Canada
140 Patel Canada
141 Rajs Canada
142 Davies Canada
143 Matos Canada
144 Vargas Canada
145 Russell USA
146 Partners USA
147 Errazuriz USA
148 Cambrault USA
149 Zlotkey USA

Rows 1 - 50. More rows exist.

 

在Order By子句中使用单列子查询

SELECT employee_id, last_name
FROM hr.employees e
ORDER BY (SELECT department_name
          FROM hr.departments d
          WHERE e.department_id = d.department_id);
EMPLOYEE_IDLAST_NAME
205 Higgins
206 Gietz
200 Whalen
100 King
101 Kochhar
102 De Haan
109 Faviet
108 Greenberg
112 Urman
111 Sciarra
110 Chen
113 Popp
203 Mavris
107 Lorentz
106 Pataballa
105 Austin
103 Hunold
104 Ernst
201 Hartstein
202 Fay
204 Baer
119 Colmenares
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
167 Banda
168 Ozer
169 Bloom
170 Fox
171 Smith
172 Bates
173 Kumar
174 Abel
175 Hutton
176 Taylor
177 Livingston
179 Johnson
145 Russell
146 Partners
147 Errazuriz
148 Cambrault
149 Zlotkey
150 Tucker
151 Bernstein
152 Hall
153 Olsen
154 Cambrault
155 Tuvault

Rows 1 - 50. More rows exist.

 

相关子查询

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

GET:从主查询中获取候选列

EXECUTE:子查询使用主查询的数据:

USE:如果满足内查询的条件则返回该行

 

D13

子查询使用主查询中的列。

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

示例：

SELECT last_name, salary, department_id
FROM hr.employees outer
WHERE salary > (SELECT AVG(salary)
                FROM hr.employees
                WHERE department_id = outer.department_id) ;
LAST_NAMESALARYDEPARTMENT_ID
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
Vollman 6500 50
Mourgos 5800 50
Ladwig 3600 50
Rajs 3500 50
Russell 14000 80
Partners 13500 80
Errazuriz 12000 80
Cambrault 11000 80
Zlotkey 10500 80
Tucker 10000 80
Bernstein 9500 80
Hall 9000 80
King 10000 80
Sully 9500 80
McEwen 9000 80
Vishney 10500 80
Greene 9500 80
Ozer 11500 80
Bloom 10000 80
Fox 9600 80
Abel 11000 80
Sarchand 4200 50
Bull 4100 50
Chung 3800 50
Dilly 3600 50
Bell 4000 50
Everett 3900 50
Hartstein 13000 20
Higgins 12008 110

38 rows selected.

注：主查询的每一行，需要进行子查询后才能判断是否输出。

 

示例2：

SELECT e.employee_id, last_name, e.job_id
FROM hr.employees e 
WHERE 2 <= (SELECT COUNT(*)
            FROM hr.job_history 
            WHERE employee_id = e.employee_id);
EMPLOYEE_IDLAST_NAMEJOB_ID
101 Kochhar AD_VP
176 Taylor SA_REP
200 Whalen AD_ASST

3 rows selected.

注：主查询的每一行取出的employee_id，在子查询中使用得到结果，比较后确定是否输出。

 

 

EXISTS操作符

EXISTS 操作符检查在子查询中是否存在满足条件的行

如果在子查询中存在满足条件的行:
–不在子查询中继续查找
–条件返回TRUE

如果在子查询中不存在满足条件的行:
–条件返回FALSE
–继续在子查询中查找

示例：

SELECT employee_id, last_name, job_id, department_id
FROM hr.employees outer
WHERE EXISTS ( SELECT 'X'
            FROM hr.employees
            WHERE manager_id = outer.employee_id);
EMPLOYEE_IDLAST_NAMEJOB_IDDEPARTMENT_ID
100 King AD_PRES 90
101 Kochhar AD_VP 90
102 De Haan AD_VP 90
103 Hunold IT_PROG 60
108 Greenberg FI_MGR 100
114 Raphaely PU_MAN 30
120 Weiss ST_MAN 50
121 Fripp ST_MAN 50
122 Kaufling ST_MAN 50
123 Vollman ST_MAN 50
124 Mourgos ST_MAN 50
145 Russell SA_MAN 80
146 Partners SA_MAN 80
147 Errazuriz SA_MAN 80
148 Cambrault SA_MAN 80
149 Zlotkey SA_MAN 80
201 Hartstein MK_MAN 20
205 Higgins AC_MGR 110

18 rows selected.

 

NOT EXISTS 操作符

示例：

SELECT department_id, department_name
FROM hr.departments d
WHERE NOT EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE department_id = d.department_id);
DEPARTMENT_IDDEPARTMENT_NAME
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll

16 rows selected.

注：查询结果为没有在employee中出现的department。

 

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

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

示例：

ALTER TABLE employees
ADD (department_name VARCHAR2(14));

注：增加列。

UPDATE employees e
SET department_name = (SELECT department_name 
                        FROM departments d
                        WHERE e.department_id = d.department_id);

注：新增的列赋值：使用子查询。

 

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

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

示例：

DELETE FROM employees e
WHERE employee_id = (SELECT employee_id
    FROM emp_history 
    WHERE employee_id = e.employee_id);

 

WITH子句

使用WITH子句, 可以避免在SELECT语句中重复书写相同的语句块，可以提高查询效率
WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中。

示例：

WITH 
dept_costs AS (SELECT d.department_name, SUM(e.salary) AS dept_total
                FROM employees e, departments d
                WHERE e.department_id = d.department_id
                GROUP BY d.department_name),
avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg
                FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >(SELECT dept_avg 
                    FROM avg_cost)
ORDER BY department_name;

 

DEPARTMENT_NAMEDEPT_TOTAL
Sales 304500
Shipping 156400

2 rows selected.

 

总结：

本节介绍高级子查询内容：

1）多列子查询：主查询的列成对/不成对与子查询的列比较；

2）单列子查询：可以在DECODE/CASE等语句使用；

3）相关子查询：主查询的每一行都执行一次子查询；

4）EXISTS和NOT EXISTS关键字：判断子查询是否存在满足条件的行；

5）相关更新和相关删除：使用相关子查询，依据一个表中的数据更新/删除另一个表的数据；

6）WITH子句：执行语句块并将结果存入用户的临时空间，避免重复执行语句块。

 

posted @ 2020-07-24 10:47  workingdiary  阅读(146)  评论(0)    收藏  举报