SQL堂上作业六
子查询
问题背景是这样的：
公司里面有若干个人，现在你需要查询公司里有多少个人工资比张三高。
但是，张三的工资也需要从表中进行查询。
这个时候就需要用到“子查询”
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
我们发现，有两个SELECT语句的嵌套
输出如下：
LAST_NAME
--------------------------------------------------
King
Kochhar
De Haan
Greenberg
Russell
Partners
Errazuriz
Ozer
Hartstein
Higgins
已选择10行。
多个子查询
在有多个需要查询的元素是，则需要分别列出子查询语句
SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
输出如下
LAST_NAME JOB_ID SALARY
-------------------------------------------------- -------------------- ----------
Nayer ST_CLERK 3200
Mikkilineni ST_CLERK 2700
Bissot ST_CLERK 3300
Atkinson ST_CLERK 2800
Mallin ST_CLERK 3300
Rogers ST_CLERK 2900
Ladwig ST_CLERK 3600
Stiles ST_CLERK 3200
Seo ST_CLERK 2700
Rajs ST_CLERK 3500
Davies ST_CLERK 3100
已选择11行。
子句套组函数
我们套了个MIN函数
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
输出如下：
LAST_NAME JOB_ID SALARY
-------------------------------------------------- -------------------- ----------
Olson ST_CLERK 2100
子句套HAVING语句
注意：当HAVING与子查询嵌套时，先计算子查询，再计算HAVING
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
输出如下：
DEPARTMENT_ID MIN(SALARY)
------------- -----------
100 6900
30 2500
7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400
已选择11行。
错误排查一
有如下的语句：
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
在运行的时候，它出现了错误，提示如下：
(SELECT MIN(salary)
*
第 4 行出现错误:
ORA-01427: 单行子查询返回多个行
等于的数据不能有多个返回行
多返回值的支持
在刚刚的查询中，子SELECT返回了多个数，能不能支持下同时对多个数进行查询？
答案是可以的，我们需要套一个子句上去。
这个子句，有IN，有ANY，有ALL。
ANY语句
下面的子句中，会返回9000,6000，4200
salary<ANY(9000,6000,4200)即为，满足其中任意一个即可。
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
输出如下：
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- -------------------------------------------------- -------------------- ----------
132 Olson ST_CLERK 2100
136 Philtanker ST_CLERK 2200
128 Markle ST_CLERK 2200
135 Gee ST_CLERK 2400
127 Landry ST_CLERK 2400
191 Perkins SH_CLERK 2500
182 Sullivan SH_CLERK 2500
144 Vargas ST_CLERK 2500
140 Patel ST_CLERK 2500
131 Marlow ST_CLERK 2500
119 Colmenares PU_CLERK 2500
118 Himuro PU_CLERK 2600
198 OConnell SH_CLERK 2600
199 Grant SH_CLERK 2600
143 Matos ST_CLERK 2600
126 Mikkilineni ST_CLERK 2700
139 Seo ST_CLERK 2700
117 Tobias PU_CLERK 2800
183 Geoni SH_CLERK 2800
130 Atkinson ST_CLERK 2800
195 Jones SH_CLERK 2800
190 Gates SH_CLERK 2900
116 Baida PU_CLERK 2900
134 Rogers ST_CLERK 2900
197 Feeney SH_CLERK 3000
187 Cabrio SH_CLERK 3000
115 Khoo PU_CLERK 3100
196 Walsh SH_CLERK 3100
142 Davies ST_CLERK 3100
181 Fleaur SH_CLERK 3100
194 McCain SH_CLERK 3200
138 Stiles ST_CLERK 3200
125 Nayer ST_CLERK 3200
180 Taylor SH_CLERK 3200
129 Bissot ST_CLERK 3300
133 Mallin ST_CLERK 3300
186 Dellinger SH_CLERK 3400
141 Rajs ST_CLERK 3500
137 Ladwig ST_CLERK 3600
189 Dilly SH_CLERK 3600
188 Chung SH_CLERK 3800
193 Everett SH_CLERK 3900
192 Bell SH_CLERK 4000
185 Bull SH_CLERK 4100
184 Sarchand SH_CLERK 4200
200 Whalen AD_ASST 4400
124 Mourgos ST_MAN 5800
202 Fay MK_REP 6000
173 Kumar SA_REP 6100
179 Johnson SA_REP 6200
167 Banda SA_REP 6200
166 Ande SA_REP 6400
123 Vollman ST_MAN 6500
203 Mavris HR_REP 6500
165 Lee SA_REP 6800
113 Popp FI_ACCOUNT 6900
161 Sewall SA_REP 7000
178 Grant SA_REP 7000
155 Tuvault SA_REP 7000
164 Marvins SA_REP 7200
172 Bates SA_REP 7300
171 Smith SA_REP 7400
154 Cambrault SA_REP 7500
160 Doran SA_REP 7500
111 Sciarra FI_ACCOUNT 7700
112 Urman FI_ACCOUNT 7800
122 Kaufling ST_MAN 7900
153 Olsen SA_REP 8000
159 Smith SA_REP 8000
120 Weiss ST_MAN 8000
121 Fripp ST_MAN 8200
110 Chen FI_ACCOUNT 8200
206 Gietz AC_ACCOUNT 8300
177 Livingston SA_REP 8400
176 Taylor SA_REP 8600
175 Hutton SA_REP 8800
已选择76行。
ALL语句
我们可以使用ALL语句，其要求是满足返回的所有的值
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
输出如下：
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- -------------------------------------------------- -------------------- ----------
185 Bull SH_CLERK 4100
192 Bell SH_CLERK 4000
193 Everett SH_CLERK 3900
188 Chung SH_CLERK 3800
137 Ladwig ST_CLERK 3600
189 Dilly SH_CLERK 3600
141 Rajs ST_CLERK 3500
186 Dellinger SH_CLERK 3400
133 Mallin ST_CLERK 3300
129 Bissot ST_CLERK 3300
180 Taylor SH_CLERK 3200
138 Stiles ST_CLERK 3200
125 Nayer ST_CLERK 3200
194 McCain SH_CLERK 3200
115 Khoo PU_CLERK 3100
196 Walsh SH_CLERK 3100
142 Davies ST_CLERK 3100
181 Fleaur SH_CLERK 3100
187 Cabrio SH_CLERK 3000
197 Feeney SH_CLERK 3000
116 Baida PU_CLERK 2900
190 Gates SH_CLERK 2900
134 Rogers ST_CLERK 2900
183 Geoni SH_CLERK 2800
130 Atkinson ST_CLERK 2800
117 Tobias PU_CLERK 2800
195 Jones SH_CLERK 2800
126 Mikkilineni ST_CLERK 2700
139 Seo ST_CLERK 2700
143 Matos ST_CLERK 2600
199 Grant SH_CLERK 2600
118 Himuro PU_CLERK 2600
198 OConnell SH_CLERK 2600
140 Patel ST_CLERK 2500
131 Marlow ST_CLERK 2500
119 Colmenares PU_CLERK 2500
191 Perkins SH_CLERK 2500
182 Sullivan SH_CLERK 2500
144 Vargas ST_CLERK 2500
127 Landry ST_CLERK 2400
135 Gee ST_CLERK 2400
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
132 Olson ST_CLERK 2100
已选择44行。
空值在子查询中出现
SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);
注意：上面的语句将导致emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr);返回空值
输出如下：
未选定行
