MySQL where后面的列子查询使用

多行子查询

• 返回多行
• 使用多行操作比较操作符

操作符含义

IN/NOT IN	等于列表中 任意一个
ANY/SOME	和子查询返回的 某一个值 比较
ALL+	和子查询返回的 所有值 比较

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

#①查询location_id是1400或1700的部门编号

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

#②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name FROM employees WHERE department_id in(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700) );

#或​​​​​​​

SELECT last_name FROM employees WHERE department_id = ANY(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700) );

注意：not in 等价于 <> ALL​​​​​​​

SELECT last_name FROM employees WHERE department_id not in(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700) );

等价于​​​​​​​

SELECT last_name FROM employees WHERE department_i <> ALL(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700) );

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

#①查询job_id为‘IT_PROG’部门任一工资​​​​​​​

SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'

#②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个​​​​​​​

SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY(SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG';

#或​​​​​​​

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

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

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

#或​​​​​​​

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

总结

以上是生活随笔为你收集整理的MySQL where后面的列子查询使用的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。