子查询

runtao.zhong 于 2026-08-07 发布

关键字

关键字 说明
IN 在集合中
ANY 满足任意一个
ALL 满足全部
SOME 同 ANY

示例：查询平均工资最低的部门

方式一：子查询嵌套

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
    ) t_dept_avg_sal  -- 子查询结果需取别名
);

方式二：使用 ALL 简化

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

注意事项