简介

  • 也称为集合比较子查询
  • 内查询返回多行数据
  • 使用多行比较操作符

    多行比较操作符

    | 操作符 | 含义 | | —- | —- | | IN | 等于列表中的任意一个 | | ANY | 需要和单行比较操作符一起使用,和子查询返回的某一个值比较 | | ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 | | SOME | 实际上是ANY的别名,作用相同,一般常使用ANY |

案例:

  1. # IN:
  2. SELECT employee_id, last_name
  3. FROM employees
  4. WHERE salary IN
  5. (SELECT MIN(salary)
  6. FROM employees
  7. GROUP BY department_id);

需求:
返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
(理解:比job_id为‘IT_PROG’部门其中一个工资低即可)

  1. SELECT employee_id,last_name,job_id,salary
  2. FROM employees
  3. WHERE job_id <> 'IT_PROG'
  4. AND salary < ANY (
  5. SELECT salary
  6. FROM employees
  7. WHERE job_id = 'IT_PROG'
  8. );

需求:
返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
(理解:比job_id为‘IT_PROG’部门每一个工资都低)

  1. SELECT employee_id,last_name,job_id,salary
  2. FROM employees
  3. WHERE job_id <> 'IT_PROG'
  4. AND salary < ALL (
  5. SELECT salary
  6. FROM employees
  7. WHERE job_id = 'IT_PROG'
  8. );

需求:查询平均工资最低的部门id
注意:MySQL中聚合函数是不能嵌套使用的。

  1. SELECT department_id
  2. FROM employees
  3. GROUP BY department_id
  4. HAVING AVG(salary) = (
  5. SELECT MIN(avg_sal)
  6. FROM(
  7. SELECT AVG(salary) avg_sal
  8. FROM employees
  9. GROUP BY department_id
  10. ) bieming
  11. );
  12. #方式2:
  13. SELECT department_id
  14. FROM employees
  15. GROUP BY department_id
  16. HAVING AVG(salary) <= ALL(
  17. SELECT AVG(salary) avg_sal
  18. FROM employees
  19. GROUP BY department_id
  20. ) ;