简介
- 也称为集合比较子查询
- 内查询返回多行数据
- 使用多行比较操作符
多行比较操作符
| 操作符 | 含义 | | —- | —- | | IN | 等于列表中的任意一个 | | ANY | 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 | | ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 | | SOME | 实际上是ANY的别名,作用相同,一般常使用ANY |
案例:
# IN:
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
需求:
返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
(理解:比job_id为‘IT_PROG’部门其中一个工资低即可)
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'
);
需求:
返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
(理解:比job_id为‘IT_PROG’部门每一个工资都低)
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
需求:查询平均工资最低的部门id
注意:MySQL中聚合函数是不能嵌套使用的。
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(avg_sal)
FROM(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) bieming
);
#方式2:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) ;