5.2.4 题目4
查询与141号员工的manager_id和department_id相同的其他员工的employee_id， manager_id，department_id
SELECT employee_id, manager_id, department_id FROM employees # 与141号员工的manager_id相同 WHERE manager_id = ( SELECT manager_id FROM employees WHERE employee_id = 141 ) # 与141号员工的department_id相同 AND department_id = ( SELECT department_id FROM employees WHERE employee_id = 141 ) # 其他员工 AND employee_id <> 141
5.3 HAVING 中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
查询最低工资大于50号部门最低工资的部门id和其最低工资
# 查询最低工资大于50号部门最低工资的部门id和其最低工资 SELECT department_id, MIN(salary) min_sal FROM employees GROUP BY department_id HAVING min_sal > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 );
5.4 CASE中的子查询
显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。
# 显式员工的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;
5.5 子查询中的空值问题
子查询不返回任何行，不会报错
SELECT last_name, job_id FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE last_name = 'Haas' );
查询结果为空
SELECT job_id FROM employees WHERE last_name = 'Haas';
由于子查询为空，所以主查询查询出来也为空，此种情况不会报错
5.6 非法使用子查询
多行子查询使用单行比较符
SELECT employee_id, last_name FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees GROUP BY department_id );
SELECT MIN(salary) FROM employees GROUP BY department_id;
由于子查询的结果为多行，而筛选条件中使用的为等于号，此时语法不合法，多行子查询使用单行比较符，所以报错。要进行多行的判断，使用多行子查询中的符号
IN
6. 多行子查询
多行子查询也称为集合比较子查询，多行子查询就是子查询返回多行数据，多行子查询使用多行比较操作符。
6.1 多行比较操作符
6.2 示例
6.2.1 IN
查询所有部门最低工资的员工的信息
SELECT employee_id, last_name FROM employees WHERE salary IN ( SELECT MIN(salary) FROM employees GROUP BY department_id );
6.2.2 ANY / ALL
返回其它job_id中比 job_id为‘IT_PROG’的任一员工工资低 的员工号、姓名、job_id 以及salary
# 返回其它job_id中比 job_id为‘IT_PROG’的任一员工工资低 的员工号、姓名、job_id 以及salary SELECT employee_id, last_name, job_id, salary FROM employees # 其它job_id WHERE job_id <> 'IT_PROG' AND # 比job_id为‘IT_PROG’的任一员工工资低 salary <ANY ( # job_id为‘IT_PROG’的员工工资 SELECT salary FROM employees WHERE job_id = 'IT_PROG' );
返回其它job_id中比 job_id为‘IT_PROG’的所有员工工资低 的员工号、姓名、job_id 以及salary
# 返回其它job_id中比 job_id为‘IT_PROG’的所有员工工资低 的员工号、姓名、job_id 以及salary SELECT employee_id, last_name, job_id, salary FROM employees # 其它job_id WHERE job_id <> 'IT_PROG' AND # 比job_id为‘IT_PROG’的所有员工工资低 salary <ALL ( # job_id为‘IT_PROG’的员工工资 SELECT salary FROM employees WHERE job_id = 'IT_PROG' );
