mysql - 使用子查询显示具有相同值的多个答案

我想展示这个奖项最高的产品（按制造商分组）。但是，如果两种产品都获得相同的奖励，SQL会向我展示其中一种。

SELECT name, manufacturer, MAX(prize)
FROM products p
GROUP BY manufacturer
HAVING MAX(prize) = (SELECT MAX(p1.prize)
                  FROM products p1
                  WHERE p.manufacturer = p1.manufacturer
                  LIMIT 1
                 );

最佳答案

首先按制造商选择所有最高奖项组，然后加入主表：

select p.* from
(select manufacturer, MAX(prize) AS maxprize from products GROUP BY manufacturer) AS m
inner join products p
on m.manufacturer = p.manufacturer and m.maxprize = p.prize
order by p.manufacturer, p.name

见demo

关于mysql - 使用子查询显示具有相同值的多个答案，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53945913/