SQL查询多类别分组下字段最大值对应完整记录

需求说明 #

从fruitstat表中查询任意数量水果类别下，各自重量最大的对应完整记录。
原先尝试的SQL写法如下，执行后无法返回预期的完整行结果：

SELECT fruits,max(weight) OVER(PARTITION BY fruits) 
FROM fruitstat
GROUP BY fruits;

原始表数据（标注*的行为期望返回结果） #

问题原因 #

原写法混用了窗口函数和GROUP BY聚合逻辑：GROUP BY fruits会将同水果类别的多行数据压缩为单条，无法返回完整行的其他字段（比如颜色）；同时窗口函数计算出的分组最大值没有和当前行的重量做匹配筛选，最终结果不符合要求。

可直接使用的实现方案 #

方案1：窗口函数写法（推荐，适配支持SQL窗口函数的数据库：MySQL 8.0+、PostgreSQL、Oracle、SQL Server等） #

用ROW_NUMBER()给每个水果分组内的记录按重量从高到低编号，最终只取每个分组编号为1的记录即可，自动适配任意数量的水果类别：

-- 如果表字段为英文，将中文列名替换为对应英文字段（fruits、color、weight）即可
SELECT 水果, 颜色, 重量
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY 水果 ORDER BY 重量 DESC) AS weight_rank
    FROM fruitstat
) t
WHERE weight_rank = 1

补充：如果同一水果类别下存在多条重量相等且都是最大值的记录，需要返回所有并列最大值记录的话，把ROW_NUMBER()替换为RANK()即可。

方案2：关联聚合查询写法（兼容不支持窗口函数的低版本数据库） #

先聚合查询出每个水果类别的最大重量，再通过关联条件匹配原表中重量等于对应类别最大值的完整记录：

SELECT f.*
FROM fruitstat f
JOIN (
    SELECT 水果, MAX(重量) AS max_weight
    FROM fruitstat
    GROUP BY 水果
) agg ON f.水果 = agg.水果 AND f.重量 = agg.max_weight

该写法默认会返回所有并列最大值的记录，不需要额外调整函数。

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