[打卡]多行子查询

#非法子查询

#多行子查询
-- 也叫集合比较子查询
-- 或内查询返回多行
-- 或使用多行比较操作符

-- 多行子查询比较操作符
-- in等于列表中的任意一个
-- any需要和单行比较操作符一起使用，和子查询返回的某一个值比较
-- all需要和单行比较操作符一起使用，和子查询返回的所有值比较
-- some实际上是any的别名，和any所用相同，一般常使用any

SELECT employee_id,last_name
from employees
where salary IN (
SELECT MIN(salary)
from employees
GROUP BY department_id
);

#any/all
#习题：返回其他job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary

-- 任一： any
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'
);

-- 所有： all
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'
);

#习题：返回公司工资最少的员工的last_name，job_id和salary

SELECT last_name,job_id,salary
from employees
where salary=(
SELECT MIN(salary)
from employees
);

#习题：查询与141号员工的 manager_id和 department_id相同的其他员工的 employee_id,manager_id,department_id

SELECT employee_id,manager_id,department_id
from employees
where manager_id=(
SELECT manager_id
from employees
where employee_id=141
)
and department_id=(
SELECT department_id
from employees
where employee_id=141
)
and employee_id<>141;

#习题：查询平均工资最低的部门ID
-- 错误写法
SELECT MIN(avg(salary))
from employees
GROUP BY department_id;

#MySQL中聚合函数不能嵌套
SELECT department_id,AVG(salary)
from employees
GROUP BY department_id;