Oracle中GROUP BY后如何获取某字段最大值对应行？

Question

在Oracle数据库中，使用 GROUP BY 对数据进行分组时，常需获取每组中某字段最大值对应的完整记录行（如获取每个部门工资最高的员工信息）。然而，直接使用 MAX() 聚合函数只能返回该字段的最值，无法同时获取该行其他字段（如姓名、入职日期等）的信息。若仅将非分组字段加入 SELECT 而未包含在 GROUP BY 中，会引发 ORA-00979 错误。如何在保证正确分组的前提下，精准获取最大值所在行的全部字段内容，成为SQL查询中的常见难题。

Answer 1

一、问题背景与核心挑战

在Oracle数据库中，使用 GROUP BY 对数据进行分组时，常需获取每组中某字段最大值对应的完整记录行。例如：获取每个部门工资最高的员工信息（包括姓名、入职日期、职位等）。然而，直接使用 MAX() 聚合函数只能返回该字段的最值，无法同时获取该行其他字段的信息。

若尝试将非分组字段（如员工姓名）加入 SELECT 列表而未包含在 GROUP BY 中，Oracle 会抛出 ORA-00979: not a GROUP BY expression 错误。这限制了开发者在保持语义正确性的同时获取完整记录的能力。

1.1 典型错误示例

SELECT dept_id, emp_name, MAX(salary)
FROM employees
GROUP BY dept_id;
-- 报错：ORA-00979

二、解决方案演进路径

2.1 方案一：子查询 + 关联条件（传统方法）

通过子查询先找出每组的最大值，再与原表关联以获取完整记录。

dept_id	emp_name	salary	hire_date
10	张伟	8000	2020-03-15
10	李娜	9500	2019-06-22
20	王强	7200	2021-01-10
20	赵敏	7200	2018-11-05

1. 先按部门分组求出最高工资
2. 然后连接原表获取对应员工的全部信息

SELECT e.dept_id, e.emp_name, e.salary, e.hire_date
FROM employees e
INNER JOIN (
    SELECT dept_id, MAX(salary) AS max_sal
    FROM employees
    GROUP BY dept_id
) t ON e.dept_id = t.dept_id AND e.salary = t.max_sal;

2.2 方案二：分析函数 ROW_NUMBER() 实现精确去重

利用窗口函数为每组内记录按 salary 排序并编号，取排名为 1 的记录。

SELECT dept_id, emp_name, salary, hire_date
FROM (
    SELECT dept_id, emp_name, salary, hire_date,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, hire_date ASC) AS rn
    FROM employees
)
WHERE rn = 1;

• PARTITION BY dept_id：按部门分组
• ORDER BY salary DESC：优先取最高工资
• hire_date ASC：相同工资时取最早入职者（可调整）

2.3 方案三：使用 KEEP (DENSE_RANK LAST) 高级聚合语法

Oracle 特有语法，允许在聚合中保留特定排序下的字段值。

SELECT 
    dept_id,
    MAX(emp_name) KEEP (DENSE_RANK LAST ORDER BY salary) AS top_emp,
    MAX(salary) AS max_salary,
    MAX(hire_date) KEEP (DENSE_RANK LAST ORDER BY salary) AS hire_date
FROM employees
GROUP BY dept_id;

此方式避免了自连接或子查询，性能更优，适合大数据量场景。

三、方案对比与适用场景分析

方案	可读性	性能	处理并列情况	推荐程度
子查询+JOIN	高	中	可能返回多行	★★★☆☆
ROW_NUMBER()	中	高	仅返回一行	★★★★★
KEEP(DENSE_RANK)	低（需学习）	极高	灵活控制	★★★★☆

3.1 性能优化建议

1. 确保 dept_id 和 salary 字段上有索引
2. 对大表使用物化视图预计算分组最大值
3. 避免在函数中嵌套过多子查询
4. 使用执行计划（EXPLAIN PLAN）分析各方案成本

3.2 处理“并列第一”的业务逻辑设计

当多个员工工资相同时，是否应返回所有最高薪员工？可通过以下方式实现：

-- 使用 RANK() 替代 ROW_NUMBER() 可返回所有并列第一
SELECT dept_id, emp_name, salary, hire_date
FROM (
    SELECT dept_id, emp_name, salary, hire_date,
           RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employees
)
WHERE rk = 1;

四、可视化流程图：决策路径选择

graph TD A[开始] --> B{是否需要返回所有并列第一?} B -- 是 --> C[使用 RANK() 或 DENSE_RANK()] B -- 否 --> D[使用 ROW_NUMBER()] D --> E{是否追求极致性能?} E -- 是 --> F[考虑 KEEP(DENSE_RANK LAST)] E -- 否 --> G[采用子查询+JOIN] C --> H[输出结果] F --> H G --> H