mysql group by返回一个最小值，并获取相应的行数据 [英] mysql group by to return a the min value and get the corresponding row data

问题描述

我有一个像这样的数据表:

I have a table of data like so :

- PK_table - merchantName - price - Product
- 1        - argos        - 7     - 4
- 2        - comet        - 3     - 4
- 1        - Dixon        - 1     - 3
- 1        - argos        - 10    - 4

我希望在mysql中选择产品的最低价格和相应的商家.

I wish to select the minimum price for a product and the corresponding merchant in mysql.

我尝试过:

SELECT Product, merchantName, min(price)
FROM a_table
GROUP BY product

但是返回的结果是错误的，因为它选择的是第一个商家名称，而不是MIN的相应商家.

however the result returned is incorrect since it chooses the first merchant name and not the corresponding merchant of the MIN.

你如何做到的?

推荐答案

SELECT Merchant.Product, Merchant.Name, Merchant.Price
FROM a_table AS Merchant
JOIN
(
SELECT Product, MIN(Price) AS MinPrice
FROM a_table
GROUP BY Product
) AS Price
ON Merchant.Product = Price.Product
AND Merchant.Price = Price.MinPrice

如果两个商人具有相同的低价，低价，则将返回两行.

Will return two rows if two merchants have the same low, low price.

这篇关于mysql group by返回一个最小值，并获取相应的行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！