【MySQL】详解20道例题带你学习子查询，偷偷做卷王(二）

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;
#解题思路：分别查询141号员工的manager_id和department_id，并且最终要出去141号员工本身

SELECT department_id , MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > 
    (SELECT MIN(salary) FROM employees 
      WHERE department_id = 50);
#解题思路:因为有聚合函数，所以要考虑分组查询；按照department_id分组后查询其最低工资大于50号部门的最低工资，这里用到了having中的子查询

显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’，这里用到了流程控制，小伙伴们要是流程控制不熟悉的话，可以看看我写的流程控制总结：MySQL流程控制大总结
SELECT employee_id, last_name,
       (CASE department_id
        WHEN
             (SELECT department_id FROM departments
       WHERE location_id = 1800)           
        THEN 'Canada' 
        ELSE 'USA' 
        END) AS location
FROM   employees;
#这里用到了case中嵌套子查询

employee_id，manager_id，department_id
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id IN(
  SELECT manager_id FROM employees WHERE employee_id IN (141,171)
)
AND department_id IN (
  SELECT department_id FROM employees WHERE employee_id IN (141,174)
)
AND employee_id <> 141 AND 174;
#这里与例题3类似，只是返回的结果是多个值，属于多行子查询

job_id 以及salary
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'
  );
#这里重点是使用关键字any，意思就是满足其中一个条件即可

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
    SELECT MIN(avg_sal)
    FROM (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    ) dept_avg_sal
    )
#解题思路：1、先根据部门id分组查询各个部门的平均工资
   2、然后把 1 中的查询结果当成一个新的表，并且要给该表取表名(dept_avg_sal)
   3、然后从新表中查询最低的平均工资
   4、1 2 3 步一起组成一个内查询，实现题目要求
#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
)
#对比方式一，这个就比较好理解了，要查询的平均工资只要不大于所有部门的平均工资就可以了

SELECT employee_id,salary
FROM employees e
ORDER BY (
   SELECT department_name
   FROM departments d
   WHERE e.`department_id` = d.`department_id`
  );
#解题思路：因为员工表中没有部门名，但是可以根据e.`department_id` = d.`department_id`
#这一条件来进行关联，即内查询用到了主查询，这就是相关子查询

UPDATE employees e
SET department_name =  (SELECT department_name 
                        FROM   departments d
                        WHERE  e.department_id = d.department_id);
#这里用到了相关更新的操作，使用相关子查询依据一个表中的数据更新另一个表的数据，道理是一样的

DELETE FROM employees e
WHERE employee_id in  
           (SELECT employee_id
            FROM   emp_history 
            WHERE  employee_id = e.employee_id);
#这里用到了相关删除的操作，使用相关子查询依据一个表中的数据删除另一个表的数据

查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees 
WHERE department_id IN (
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
    );
#解题思路：还是老方法，先查询姓名中包含字母U的员工部门号，注意用到了关键字IN

SELECT last_name,salary
FROM employees
WHERE salary = (
  SELECT MIN(salary)
  FROM employees
  );
#这里用到了聚合函数

SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
        SELECT *
        FROM employees e2
        WHERE e1.`employee_id` = e2.`manager_id`
      );
#这里使用了EXISTS关键字，当e1.`employee_id` = e2.`manager_id`成立，内查询为true时，就继续执行

SELECT department_id
FROM departments d
WHERE NOT EXISTS (
  SELECT *
  FROM employees e
  WHERE d.`department_id` = e.`department_id`
  AND e.`job_id` = 'ST_CLERK'
  );
#这里用到了关键字 NOT EXISTS，与EXISTS 相反，只有为false时，才继续执行

SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
  SELECT *
  FROM employees mgr
  WHERE emp.`manager_id` = mgr.`employee_id`
  );

SELECT employee_id,last_name,hire_date,salary
FROM employees e1
WHERE EXISTS (
  SELECT *
  FROM employees e2
  WHERE e1.`manager_id` = e2.`employee_id`
  AND e2.last_name = 'De Haan'
  );

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE department_id = e1.`department_id`
  );
#这里考察相关子查询的使用

SELECT department_name
FROM departments d
WHERE 5 < (
    SELECT COUNT(*)
    FROM employees e
    WHERE d.department_id = e.`department_id`
   );

SELECT *
FROM departments
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING AVG(salary ) =(
      SELECT AVG(salary) avg_sal
      FROM employees
      GROUP BY department_id
      ORDER BY avg_sal ASC
      LIMIT 1  
      )
    );
#这里用到了LIMIT关键字，
# 1 分组排序后用limit获取最低的工资
# 2 再根据部门id分组后筛选平均工资等于 1 中的结果
# 3 根据 2 获取的查询结果，实现题目要求
# 难度有点高，实现方式不止一种。