TSQL中MAX与GROUP BY使用异常，如何获取各分类最高价格？

我有一张名为articles的表，结构和数据如下：

我需要获取每个category对应最高price的完整记录，期望结果如下：

但执行以下SQL后，category1的两条记录都被返回了，不符合预期：

select Max(price), ID, category from article group by ID,category

返回结果：

你的SQL按ID, category分组，由于ID是唯一值，每条记录都会被划分为独立分组，MAX(price)实际就是每条记录自身的价格，因此会返回所有原始记录，完全不符合按分类取最高价的需求。

方法1：窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等） #

使用ROW_NUMBER()窗口函数按分类分组，给每个分组内的记录按价格降序编号，取编号为1的记录：

SELECT ID, category, price
FROM (
    SELECT 
        ID, 
        category, 
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM articles
) t
WHERE rn = 1;

如果同一分类存在多条价格相同的最高记录，这个方法只会返回其中一条（若需指定排序规则，可在ORDER BY后加额外字段，比如ORDER BY price DESC, ID DESC）。

方法2：子查询关联（兼容低版本数据库） #

先通过子查询获取每个分类的最高价格，再关联原表匹配对应记录：

SELECT a.ID, a.category, a.price
FROM articles a
INNER JOIN (
    SELECT category, MAX(price) AS max_price
    FROM articles
    GROUP BY category
) b ON a.category = b.category AND a.price = b.max_price;

注意：如果同一分类有多条价格相同的最高记录，这个方法会返回所有符合条件的记录。

方法3：WHERE子查询 #

直接在WHERE子句中判断当前记录的价格是否为对应分类的最高价：

SELECT ID, category, price
FROM articles a
WHERE price = (
    SELECT MAX(price)
    FROM articles
    WHERE category = a.category
);

同样，若同一分类存在多个最高价记录，会全部返回。

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