Subquery子查询在MySQL中的应用实例

在MySQL中，子查询（Subquery）是一种嵌套在另一个查询中的查询。子查询可以出现在SELECT、FROM、WHERE和HAVING子句中，用于进一步限制或操作数据。以下是一些子查询的应用实例：

1. 在SELECT子句中使用子查询

实例： 查询每个部门的平均工资，并找出平均工资最高的部门。

SELECT department_id, department_name, salary
FROM departments d
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) e ON d.department_id = e.department_id
WHERE e.avg_salary = (SELECT MAX(avg_salary) FROM (SELECT AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS sub);

2. 在FROM子句中使用子查询

实例： 查询每个部门的员工数量，并将结果临时表。

SELECT d.department_id, d.department_name, dept_emp_count
FROM departments d
JOIN (
    SELECT department_id, COUNT(*) AS dept_emp_count
    FROM employees
    GROUP BY department_id
) AS dept_emp ON d.department_id = dept_emp.department_id;

3. 在WHERE子句中使用子查询

实例： 查询工资高于公司平均工资的员工。

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

4. 在HAVING子句中使用子查询

实例： 查询平均工资高于公司平均工资的部门。

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

5. 相关子查询

实例： 查询每个员工的下一个更高工资的员工。

SELECT e.employee_id, e.first_name, e.last_name, e.salary, 
       (SELECT MIN(e2.salary)
        FROM employees e2
        WHERE e2.salary > e.salary AND e2.employee_id > e.employee_id) AS next_higher_salary
FROM employees e;

6. 使用EXISTS子查询

实例： 查询有员工的部门。

SELECT department_id, department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

7. 使用NOT EXISTS子查询

实例： 查询没有员工的部门。

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

这些实例展示了子查询在MySQL中的多种应用方式，可以根据具体需求选择合适的子查询类型来解决问题。