子查询
单行子查询的思路：
SQL> select salary from employees where last_name='Feeney';
SQL> select last_name from employees where salary>3000;
SQL> select last_name from employees where salary>(select salary from employees where last_name='Feeney');
多行子查询的思路：
SQL> select distinct department_id from employees where department_id is not null;
SQL> select department_name from departments where department_id in (10, 20,30);
SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);
用多表连接改写：
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id
for dept in 1..27
for emp in 1..107
查看emp中是否出现deptid
练习：
工资大于全公司平均工资的员工姓名。
SQL> select last_name from employees where salary>(select avg(salary) from employees);
和Feeney同年入职的员工姓名
select last_name, hire_date
from employees
where extract(year from hire_date)=
(select extract(year from hire_date) from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, hire_date
from employees
where hire_date between
(select to_date(to_char(hire_date, 'yyyy')||'0101', 'yyyymmdd') from employees where last_name='Feeney')
and
(select to_date(to_char(hire_date, 'yyyy')||'1231', 'yyyymmdd') from employees where last_name='Feeney')
在Seattle工作的所有员工姓名
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
查找符合下列条件的员工姓名：和Abel在同一个部门，工资比Olson高
select last_name from employees
where department_id=
(select department_id from employees where last_name='Abel')
and salary >
(select salary from employees where last_name='Olson');
配对子查询：
和Feeney在同一个部门、做同一职位的员工姓名：
select last_name, department_id, job_id
from employees
where department_id=
(select department_id from employees where last_name='Feeney')
and job_id=
(select job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, department_id, job_id
from employees
where (department_id, job_id)=
(select department_id, job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
in和not in受null值的影响：
所有管理者的姓名：
SQL> select last_name from employees where employee_id in (select manager_id from employees);
所有普通员工的姓名：
SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);
关联子查询：
工资大于所在部门平均工资的员工姓名。
for i in 1..107所有员工
{
select avg(salary) from employees where department_id=i.department_id
if i.salary > i所在部门的平均工资
保留此记录
}
select last_name
from employees outer
where salary >
(select avg(salary) from employees
where department_id = outer.department_id);
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
exists/not exists查询：
for i in 1..27所有部门
{
for j in 1..107所有员工
{
if i.department_id = j.department_id
保留此记录
break
}
}
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select department_name
from departments outer
where not exists
(select 1 from employees where department_id=outer.department_id);
练习：
有员工的部门名称
没有员工的部门
所有普通员工的姓名
所有管理者的姓名：
for i in 1..107所有员工
{
for j in 1..107所有员工
{
if i.employee_id = j.manager_id
保留此记录
break
}
}
select last_name
from employees outer
where exists
(select 1 from employees where manager_id=outer.employee_id);
所有普通员工的姓名：
select last_name
from employees outer
where not exists
(select 1 from employees where manager_id=outer.employee_id);
子查询和多表连接的转换：
有员工的部门的名称
select department_name
from departments
where department_id in
(select department_id from employees);
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id;
练习：
在Seattle工作的所有员工姓名（使用子查询和多表连接两种方式）
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
select e.last_name
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.city='Seattle';
最大值查询：
SQL> select last_name from employees where salary=(select max(salary) from employees);
top-N查询：
SQL> select last_name, salary from employees where rownum<=3 order by salary desc;
SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;
分页查询：
SQL> select * from
(select * from
(select * from
(select last_name, salary from employees order by salary desc)
where rownum<=6)
order by salary)
where rownum<=3
order by salary desc;
SQL> select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
) v2
where row_num between 4 and 6;
select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
where rownum<=6
) v2
where row_num >= 4;
