[打卡]万物不及桐神teww

-- 多行子查询
/*
也称为集合比较子查询（有点绕口）

内查询返回多行

使用多行比较操作符

*/
/*
多行子查询比较操作符
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
);

-- ANY / ALL

-- 习题：返回其他job——id中比job——id为“IT——prog”部门任一工资低的员工的员工号、姓名、job_id以及salary

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

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <> '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 department_id,AVG(salary)
FROM employees
GROUP BY department_id;

-- mysql 中聚合函数不能嵌套

SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id;

SELECT AVG(salary) avg——sal
FROM employees
GROUP BY department_id;

-- 打不过 借口版本有问题
-- SELECT avg_sal
-- FROM (
-- SELECT AVG(salary) avg——sal
-- FROM employees
-- GROUP BY department_id
-- )t_dept_avg_sal