MySQL 子查询

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

在这里记录一些子查询方法。

一、查询平均工资最低的部门

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
                    SELECT AVG(salary) avg_sal
                    FROM employees
                    GROUP BY department_id
)

二、查询员工中工资大于本部门平均工资的员工的last——name,salary和department_id

方式一：

方式二：在form中使用子查询

SELECT last_name,salary,e1.department_id
FROM employees e1,
     (SELECT department_id,AVG(salary) dept_avg_sal 
      FROM employees 
      GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

三、查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (
        SELECT department_name
        FROM departments d
        WHERE e.`department_id` = d.`department_id`
);

四、若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (
         SELECT COUNT(*)
         FROM job_history
         WHERE employee_id = e.employee_id);

五、相关更新

在employees中增加一个department_name字段，数据为员工对应的部门名称

# 1）
ALTER TABLE employees
ADD(department_name VARCHAR2(14));

# 2）
UPDATE employees e
SET department_name = (SELECT department_name
                      FROM departments d
                      WHERE e.department_id = d.department_id);

删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees e
WHERE employee_id in
                (SELECT employee_id
                 FROM emp_history
                 WHERE employee_id = e.employee_id);