mysql - 通过与结果匹配的方式控制组上返回的行

Question

我有下表:

 ----------------------------
|  id  |  product  |  price  |
 ----------------------------
|  1   |  1        |  50     |
|  2   |  1        |  39     |
|  3   |  1        |  60     |
 ----------------------------

我需要的是获取按产品 (1) 分组的最低价格 (39) 并保留最低价格的 ID。

我有这个查询

SELECT
    id,
    product,
    MIN(price) AS price
FROM
    variant
GROUP BY
    product

结果为

 ----------------------------
|  id  |  product  |  price  |
 ----------------------------
|  1   |  1        |  39     |
 ----------------------------

但我正在寻找的结果是

 ----------------------------
|  id  |  product  |  price  |
 ----------------------------
|  2   |  1        |  39     |
 ----------------------------

有可能实现这个目标吗？

我做了一个 fiddle :http://sqlfiddle.com/#!9/d2ddb2

Answer 1

尝试:(不是很优雅，但是是进行进一步更改的良好平台)

select A.*,B.id from 
(SELECT product,MIN(price) AS minprice 
FROM variant GROUP BY product ) A
inner join 
(SELECT * FROM variant ) B
on A.product = B.product and A.minprice = B.price