MySQL group by min() 和限制输出

Question

这是我当前正在使用的 SQL 查询:

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

从此数据集中:

Product    Name         Price   
11          Merch1      19.00   
11          Merch2      20.00
11          Merch3      19.00
11          Merch4      19.50
12          Merch1      20.00   
12          Merch2      20.00
13          Merch1      17.00
13          Merch3      15.00

当价格相同时，当前的 SQL 会输出多个产品记录，如下所示:

Product    Name         Price   
11          Merch1      19.00
11          Merch3      19.00   
12          Merch1      20.00
12          Merch2      20.00   
13          Merch3      15.00

我想按产品分组并显示最低价格以及相应的行数据。如果一个产品的两个价格相同，则使用找到的第一条记录。

尝试得到这个结果:

Product    Name         Price   
11          Merch1      19.00   
12          Merch1      20.00   
13          Merch3      15.00

Answer 1

您不需要任何联接即可执行此操作。

如果您想获取商家每种产品的最低价格，您可以这样做:

SELECT Product, Name, MIN(Price) as MinPrice
FROM a_table
GROUP BY Product, Name

如果您只想要产品的最低价格，而不考虑商家，您可以这样做:

SELECT Product, MIN(Price) as MinPrice
FROM a_table
GROUP BY Product