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

1. 问题描述?

表结构:
如何分组查询最高金额的商品?

要求:
我希望查询id,product_id,price三个字段,要求是按照product_id进行分组,查询price最高的记录。

我尝试的语句:
1.SELECT id,product_id,max(price) FROM goods GROUP BY product_id;
得到的结果是:

如何分组查询最高金额的商品?
product_id和price虽然是正确的,但是明显对应的id不是正确的。

2.select id,product_id,price FROM (SELECT * FROM goods ORDER BY price DESC) as goods GROUP BY product_id;
得到的结果是:

如何分组查询最高金额的商品?
根本就没有按照price最大的进行筛选

2. 我希望得到的结果是

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

《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
讨论数量: 6
陈先生

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

1个月前 评论

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

1个月前 评论
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 只能找出对应最高数据,并不能找到同行数据

1个月前 评论

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;
1个月前 评论
SELECT
    id,
    product_id,
    price
FROM
    `goods`
WHERE
    price IN (
        SELECT
            max( price )
        FROM
            `goods`
        GROUP BY product_id
    )
1个月前 评论

先排序后分组,放在一个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 分组

1个月前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!