案例

需求：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

# 方式1：使用相关子查询
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
        SELECT AVG(salary)
        FROM employees e2
        WHERE department_id = e1.`department_id`
        );
#方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
        SELECT department_id,AVG(salary) avg_sal
        FROM employees
        GROUP BY department_id) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal;

需求：查询员工的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`
    ) ASC;

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

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