如何分组查询最高金额的商品？

这个题目前阵子刚看过，几乎一模一样，当时觉得如果查询单表的数据都要 join 的话不是一个优雅的事情，但最后也没有找到除 join 外的任何方案。

网传，切换到 PG 就有这个功能。。。

SELECT b.*
FROM
    (SELECT MAX(`price`) as `max`, `product_id` FROM `goods` GROUP BY `product_id`) AS a
LEFT JOIN `goods` AS b ON b.`price` = a.`max` AND b.product_id = a.product_id

这里有一个知识点 group 只能找出对应最高数据，并不能找到同行数据

GPT给出的答案：适用于MySQL 8.0以上

SELECT id, product_id, price
FROM (
    SELECT id, product_id, price,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY price DESC) as row_num
    FROM products
) as ranked_products
WHERE row_num = 1;

SELECT
    id,
    product_id,
    price
FROM
    `goods`
WHERE
    price IN (
        SELECT
            max( price )
        FROM
            `goods`
        GROUP BY product_id
    )

先排序后分组，放在一个SQL中则需要先子查询排序的结果，然后根据子查询结果分组，这里面的注意点是因为mysql版本SQL标准不同，SQL查询也不同，主要就是mysql5.6及以上版本的差别：

-- mysql 5.6
SELECT * FROM (SELECT `id`,`product_id`,`price` FROM goods ORDER BY price DESC) t GROUP BY t.product_id;

-- mysql 5.7
SELECT * FROM (SELECT `id`,`product_id`,`price` FROM goods ORDER BY price DESC LIMIT 100) t GROUP BY t.product_id;

-- mysql 8.0 除了5.7写法外，也可以使用窗口函数
详见4楼chatgpt答案

-- PS : 5.7以及8.0版本 默认启用 `only_full_group_by`，查询的字段中包含非分组字段会报错，要么重新设置sqlmode 移除`only_full_group_by`，要么在未分组的字段中使用函数ANY_VALUE(`field`) AS `field`替代

参考：
Mysql 5.7 排序
Mysql 5.7 分组