子查询

子查询在主查询之前一次执行完成,子查询的结果被主查询使用

1.单行子查询：
查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select employee_id,last_name,salary from employees e1
where salary > (
select avg(salary) from employees e2
where e1.department_id = e2.department_id
group by department_id
)

2.多行子查询：
IN：等于列表中的任意一个
ANY：和子查询返回的某一个值比较
ALL：和子查询返回的所有值比较
返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
select employee_id,last_name,job_id,salary from employees
where job_id <> ‘IT_PROG’
and salary<any(select salary from employees where job_id = ‘IT_PROG’)

查询出公司中所有 manager 的详细信息.(注意重复的问题)
SELECT employee_id, last_name FROM employees
WHERE employee_id in (
SELECT distinct manager_id FROM employees
)

查询平均工资最低的部门信息和该部门的平均工资（在后面有更简便的方法，所以了解就好）
select d.*, (select avg(salary) from employees where department_id = d.department_id)
from departments d
where d.department_id = (
SELECT department_id FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT min(avg(salary)) FROM employees
GROUP BY department_id
)
)

3.成对比较：
问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id
select employee_id from employees
where (manager_id,department_id) in(
select manager_id,department_id from employees
where employee_id in(141,174)
)
and employee_id not in(141,174)

4.在 FROM 子句中使用子查询
问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
前面有相应的方法，但比较繁琐，在from子句中使用子查询：
select last_name,e1.department_id,salary,e2.avg_sal
from employees e1,(select department_id,avg(salary) avg_sal from employees group by department_id) e2
where e1.department_id = e2.department_id

5.在CASE表达式中使用单列子查询
问题：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id
为1800的department_id相同，则location为’Canada’,其余则为’USA’。
select employee_id,last_name,
(case department_id when(select department_id from departments
where location_id = 1800) then ‘Canada’
else ‘USA’ end)location
from employees

6.在 ORDER BY 子句中使用单列子查询
问题：查询员工的employee_id,last_name,要求按照员工的department_name排序
select employee_id,last_name from employees e1
order by(
select department_name from departments d
where e1.department_id = d.department_id
)

7.相关子查询：相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
问题：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，
输出这些相同id的员工的employee_id,last_name和其job_id
select employee_id,last_name,job_id from employees e1
where 2 <= (select count(*) from job_history where employee_id = e1.employee_id)