黑猴子的家：mysql 多行子查询

多行子查询

1、案例1：返回location_id是1400或1700的部门中的所有员工姓名

（1）查询location_id是1400或1700的部门编号

SELECT department_id FROM departments
WHERE location_id IN(1400,1700)

（2）查询department_id满足①结果的员工姓名

SELECT last_name FROM employees
WHERE department_id IN(
    SELECT department_id FROM departments
    WHERE location_id IN(1400,1700)
)

2、案例2：返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员工号、姓名、job_id 以及salary

（1）查询job_id为‘IT_PROG’部门工资

SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'

（2）返回其它部门中，工资<any ①的结果

SELECT  last_name,employee_id,job_id,salary
FROM employees 
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id<>'IT_PROG';

3、案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT  last_name,employee_id,job_id,salary
FROM employees 
WHERE salary<ALL(
    SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id<>'IT_PROG';