Oracle SQL查询分组列极值及对应行所有字段的实现方法

如何在Oracle SQL查询中获取MIN(salary)、MAX(salary)，同时返回name、DOB、phone number等所有其他列？
现有结合UNION与分组子查询关联的写法可正常运行，能实现按department_id分组查询各部门薪资最低、最高员工全部信息的需求，需要确认是否有使用分析函数的更优实现方式。
原有实现代码如下：

SELECT
    a.*
FROM
         employees a
    JOIN (
        SELECT
            MIN(salary) min_sal,
            department_id
        FROM
            employees
        GROUP BY
            department_id
    ) b ON a.salary = min_sal
           AND a.department_id = b.department_id
UNION
SELECT
    a.*
FROM
         employees a
    JOIN (
        SELECT
            MAX(salary) max_sal,
            department_id
        FROM
            employees
        GROUP BY
            department_id
    ) b ON a.salary = max_sal
           AND a.department_id = b.department_id;

直接使用Oracle窗口分析函数实现即可，相比原写法性能更好、逻辑更简洁，且和原有逻辑完全兼容：

SELECT *
FROM (
    SELECT
        e.*,
        MIN(salary) OVER (PARTITION BY department_id) AS dept_min_sal,
        MAX(salary) OVER (PARTITION BY department_id) AS dept_max_sal
    FROM employees e
)
WHERE salary IN (dept_min_sal, dept_max_sal);

方案优势 #

• 仅需对employees表做一次扫描，不需要原写法中两次聚合分组、两次JOIN关联，也省去了UNION操作自带的排序去重开销，表数据量越大，性能优势越明显
• 代码结构简洁，后续调整维护成本低
• 逻辑和原SQL完全对齐：同部门如果存在多名员工薪资并列最低/最高的情况，所有符合条件的记录都会正常返回，不会漏数

如果需要额外标记员工属于部门最低薪还是最高薪，可以增加字段做标识：

SELECT
    employee_id,
    name,
    DOB,
    phone_number,
    department_id,
    salary,
    CASE WHEN salary = dept_min_sal THEN '部门最低薪' ELSE '部门最高薪' END AS salary_tag
FROM (
    SELECT
        e.*,
        MIN(salary) OVER (PARTITION BY department_id) AS dept_min_sal,
        MAX(salary) OVER (PARTITION BY department_id) AS dept_max_sal
    FROM employees e
)
WHERE salary IN (dept_min_sal, dept_max_sal)
ORDER BY department_id, salary;

注意：如果业务要求每个部门的最低、最高薪仅返回1条记录（即使存在薪资并列），可以替换为ROW_NUMBER()分析函数，自定义排序规则后取排序序号为1的记录即可，该逻辑和原SQL逻辑不一致，需根据实际业务需求选择。

内容的提问来源于stack exchange，提问作者Erik