select查询之三：子查询
Select语句的总结构：
Select [字段] [限制条件]
from [数据表] /子查询[嵌套查询]
where [筛选条件] /子查询[嵌套查询]
group by[分组字段] /子查询[嵌套查询]
Having [辅助筛选条件] /子查询[嵌套查询]
order by[排序字段];
如以上所示，子查询或者嵌套查询就是嵌套在基本select语句中的各个字句里面，辅助主查询完成复杂的查询任务。子查询里面还可以继续嵌套select语句，叫做嵌套查询。
1、单行子查询：
查看与Sully同部门员工的信息。
SQL> select employee_id,last_name,salary,department_id
2 from employees
3 where department_id=
4 (select department_id
5 from employees
6 where last_name = 'Sully');
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
----------- ------------------------- ---------- -------------
145 Russell 14000 80
146 Partners 13500 80
147 Errazuriz 12000 80
148 Cambrault 11000 80
149 Zlotkey 10500 80
150 Tucker 10000 80
151 Bernstein 9500 80
152 Hall 9000 80
153 Olsen 8000 80
154 Cambrault 7500 80
155 Tuvault 7000 80
.....
34 rows selected.
2、多行子查询：
各部门最高工资的员工信息：多字段条件
SQL> select last_name,department_id,salary
2 from employees
3 where(department_id,salary) in
4 (select department_id,max(salary)
5 from employees
6 group by department_id);
LAST_NAME DEPARTMENT_ID SALARY
------------------------- ------------- ----------
Greenberg 100 12008
Raphaely 30 11000
Hartstein 20 13000
Baer 70 10000
King 90 24000
Higgins 110 12008
Fripp 50 8200
Mavris 40 6500
Russell 80 14000
Whalen 10 4400
Hunold 60 9000
11 rows selected.
3、where字句子查询：
该部门中工资高于1000的员工数量超过2人，列出符合条件的部门：显示部门名字。
SQL> select department_name,department_id
2 from departments
3 where department_id in
4 (select department_id
5 from employees
6 where salary >1000
7 group by department_id
8 having count(*)>2);
DEPARTMENT_NAME DEPARTMENT_ID
------------------------------ -------------
Finance 100
Purchasing 30
Executive 90
Shipping 50
Sales 80
IT 60
6 rows selected.
4、exists 或者not exists 的用法：
在HR用户，查询没有任何职员的部门。
SQL> select department_id,department_name
2 from departments d
3 where not exists(
4 select 1 from employees t
5 where d.department_id = t.department_id);
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
.....
260 Recruiting
270 Payroll
16 rows selected.
5、from字句子查询：
From子查询：查看工资在12000与14000之间员工的工资：
SQL> select * from(
2 select employee_id,last_name,salary
3 from employees
4 where salary between 12000 and 14000);
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
201 Hartstein 13000
205 Higgins 12008
108 Greenberg 12008
145 Russell 14000
146 Partners 13500
147 Errazuriz 12000
6 rows selected.
6、多列子查询：
在表emp查询与 Sully相同入职时间与相同部门员工的信息：
SQL> select employee_id,last_name,department_id,hire_date
2 from employees
3 where (department_id,hire_date)=
4 (select department_id,hire_date
5 from employees
6 where last_name = 'Sully');
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID HIRE_DATE
----------- ------------------------- ------------- ---------
157 Sully 80 04-MAR-04
在此，还有嵌套查询与having字句子查询没有列出类，用法与以上相类似，不在一一举例。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2125907/ ，如需转载，请注明出处，否则将追究法律责任。
