MySQL数据库，子查询学习，高手必备(三)
MySQL数据库，子查询学习，高手必备(三)
列⼦查询(⼦查询结果集⼀列多⾏)
列⼦查询需要搭配多⾏操作符使⽤：in(not in)、any/some、all。
为了提升效率，最好去重⼀下distinct关键字。
示例1
返回location_id是1400或1700的部门中的所有员⼯姓名
/*返回location_id是1400或1700的部门中的所有员⼯姓名*/
/*⽅式1*/
/*①查询location_id是1400或1700的部门编号*/
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700);
/*②查询员⼯姓名，要求部门是①列表中的某⼀个*/
SELECT a.last_name
FROM employees a
WHERE a.department_id IN (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
/*⽅式2：使⽤any实现*/
SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
FROM departments WHERE location_id IN (1400, 1700));
/*拓展，下⾯与not in等价*/
SELECT a.last_name
FROM employees a
WHERE a.department_id <> ALL (SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400, 1700));
示例2
返回其他⼯种中⽐jobid为'ITPROG'⼯种任意⼯资低的员⼯的员⼯号、姓名、
job_id、salary
/*返回其他⼯种中⽐job_id为'IT_PROG'⼯种任⼀⼯资低的员⼯的员⼯号、姓名、job_id、
salary*/
/*①查询job_id为'IT_PROG'部门任-⼯资*/
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
/*②查询员⼯号、姓名、job_id、salary，slary<①的任意⼀个*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < ANY (SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
/*或者*/
SELECT
last_name,
employee_id,
job_id,
salaryFROM employees
WHERE salary < (SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
示例3
返回其他⼯种中⽐jobid为'ITPROG'部门所有⼯资低的员⼯的员⼯号、姓名、
job_id、salary
/*返回其他⼯种中⽐job_id为'IT_PROG'部门所有⼯资低的员⼯的员⼯号、姓名、job_id、
salary*/
SELECT
last_name,
employee_id,
job_id,
salary
FROM employees
WHERE salary < ALL (SELECT DISTINCT salary
FROM employees
WHERE 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 employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';