php - MySQL 按 ID 分组并按 MIN(价格)排序

Question

好的，所以我有以下 MySQL 语句...

目标是按“id”代码对所有“库存”产品进行分组，并显示“最低价格”的产品。

我原以为单独按 id 分组并在 ORDER BY 子句中添加 MIN(price) 就可以完成这项工作，但显然不行...

SELECT *  
FROM (`products`)  
WHERE `in_stock` = 'yes'  
GROUP BY `id`  
ORDER BY brand, line, product, size, unit, MIN(price)  
LIMIT 25

有人可以帮忙吗？

示例数据:

id  brand   line        product size    unit    price   url
123 Adidas  Deep Energy EDT 50  ml  10.00   Link1
123 Adidas  Deep Energy EDT 50  ml  8.75    Link2
123 Adidas  Deep Energy EDT 50  ml  9.99    Link3
124 Adidas  Deep Energy EDT 100 ml  15.00   Link4

因此，实际上我尝试组合的查询将返回上述示例数据的第 2 行和第 4 行(“123”和“124”的最低价格行)

Answer 1

您计算的最低价格为:

SELECT id, MIN(price)   
FROM products
WHERE in_stock = 'yes'  
GROUP BY id ; 

要获取最低价格的行，您需要将其重新加入:

select p.*
from products p join
     (SELECT id, MIN(price) as minprice  
      FROM products
      WHERE in_stock = 'yes'  
      GROUP BY id 
     ) pmin
     on p.id = pmin.id and p.price = pmin.minprice;