MySQL 选择多个最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3590530/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select multiple maximum values
提问by Ohas
I have a table called orderwhich contains columns id, user_id, priceand item_id. Item prices aren't fixed and I would like to select each item's most expensive order. I want to select user_id, item_idand pricein the same query. I tried the following query but it doesn't return the correct result set.
我有一个名为表order包含列id，user_id，price和item_id。商品价格不固定，我想选择每件商品中最贵的订单。我想选择user_id,item_id并price在同一个查询中。我尝试了以下查询，但它没有返回正确的结果集。
SELECT user_id, item_id, MAX(price)
FROM order
GROUP BY item_id
Some of the rows returned by this query have the wrong user_id. However, all rows in the result set show each item's correct highest price.
此查询返回的某些行具有错误的user_id. 但是，结果集中的所有行都显示每个项目的正确最高价格。
回答by Daniel Vassallo
You may want to use a derived table, as follows:
您可能希望使用派生表，如下所示：
SELECT o1.item_id, o1.max_price, o2.user_id user_of_max_price
FROM (
SELECT item_id, MAX(price) max_price
FROM `order`
GROUP BY item_id
) o1
JOIN `order` o2 ON (o2.price = o1.max_price AND o2.item_id = o1.item_id)
GROUP BY o1.item_id;
Test case:
测试用例：
CREATE TABLE `order` (user_id int, item_id int, price decimal(5,2));
INSERT INTO `order` VALUES (1, 1, 10);
INSERT INTO `order` VALUES (1, 2, 15);
INSERT INTO `order` VALUES (1, 3, 8);
INSERT INTO `order` VALUES (2, 1, 20);
INSERT INTO `order` VALUES (2, 2, 6);
INSERT INTO `order` VALUES (2, 3, 15);
INSERT INTO `order` VALUES (3, 1, 18);
INSERT INTO `order` VALUES (3, 2, 13);
INSERT INTO `order` VALUES (3, 3, 10);
Result:
结果：
+---------+-----------+-------------------+
| item_id | max_price | user_of_max_price |
+---------+-----------+-------------------+
| 1 | 20.00 | 2 |
| 2 | 15.00 | 1 |
| 3 | 15.00 | 2 |
+---------+-----------+-------------------+
3 rows in set (0.00 sec)
回答by Feio
Maybe this is a little longer but you gain in readability
也许这有点长，但你会增加可读性
SELECT
*
FROM
`order`
JOIN
(
SELECT
item_id,
MAX(price) price
FROM
`order`
GROUP BY
item_id
)
USING(item_id, price);
回答by a'r
You need to first get the maximum price for each item id and then join back to orderto get records where the item was ordered for the maximum price. Something like the following query should work. Although, it will return all records with the maximum item prices.
您需要首先获取每个商品 id 的最高价格，然后返回order以获取以最高价格订购该商品的记录。类似于以下查询的内容应该可以工作。虽然，它将返回具有最高商品价格的所有记录。
SELECT user_id, item_id, price
FROM order o
JOIN (
SELECT item_id, max(price) max_price
FROM order
GROUP BY item_id
) o2
ON o.item_id = o2.item_id AND o.price = o2.max_price;
回答by bobince
This is a per-group-maximum question. There are various approachesto this common problem. On MySQL it's typically faster and simpler to use a null-self-join than anything involving subqueries:
这是一个每组最大的问题。有多种方法可以解决这个常见问题。在 MySQL 上，使用 null-self-join 通常比任何涉及子查询的都更快、更简单：
SELECT o0.user_id, o0.item_id, o0.price
FROM order AS o0
LEFT JOIN order AS o1 ON o1.item_id=o0.item_id AND o1.price>o0.price
WHERE o1.user_id IS NULL
ie. “select each row where there exists no other row for the same item with a higher price”.
IE。“选择价格更高的同一项目不存在其他行的每一行”。
(If two rows have the same maximum price you will get both returned. What exactly to do in the case of a tie is a general problem for per-group-maximum solutions.)
（如果两行具有相同的最高价格，您将获得两者都返回。在平局的情况下究竟该怎么做是每组最高解决方案的一般问题。）
回答by Alex
SELECT user_id, item_id, MAX(price)
FROM order
GROUP BY item_id
The SQL you used is contradictory by GROUP. Once you use GROUP, MYSQL will always select the FIRST user_id, but the HIGHEST price, this is the reason why the user is wrong but the price is right.
您使用的 SQL 与 GROUP 矛盾。一旦你使用了GROUP，MYSQL总是会选择FIRST user_id，但是HIGHEST价格，这就是为什么用户错误但价格正确的原因。
You can try to add ORDER BY price DESCto see what happen, but I did not try in my environment.
你可以尝试添加ORDER BY price DESC看看会发生什么，但我没有在我的环境中尝试。
回答by Lekensteyn
Your query groups the rows by item_id. If you have multiple items with item_id1, with different a user_id, it will only pick the first user_id, not the user_idwith the highest price.
您的查询按 对行进行分组item_id。如果您有多个item_id1 的项目，不同的 a user_id，它只会选择第一个user_id，而不是user_id价格最高的。
回答by StuartLC
You'll either need to group by item_id AND user_id (showing the max price per item per user), or if you want just the item in the group you'll need to rethink the user_id column. e.g. show the max price for an item and show the LAST user who made a change on the price, OR show the Max price for an item and show the user who MADE the Max Price for the item etc. Have a look at this postfor some patterns for doing this.
您要么需要按 item_id 和 user_id 分组（显示每个用户每件商品的最高价格），或者如果您只想要组中的商品，则需要重新考虑 user_id 列。例如，显示最高价格的项目，并显示谁做对价格变化的最后一个用户，或显示最高价格的项目，并显示谁提出的最高报价为项目等用户看一看这个职位的这样做的一些模式。
回答by Kapil
if you want top 2 from order try this ...
如果你想从订单中获得前 2 名，试试这个......
if you want top 3 then just change last condition
where item_rank in (1,2) ;to where item_rank in (1,2,3) ;
如果你想要前 3 名，那么只需改变最后一个条件item_rank in (1,2) ;到哪里where item_rank in (1,2,3) ;
select * from
(select item_id , price
, @curRow % @curNval as item_rank
, @curRow := @curRow + 1 AS row_number
from `order` , (SELECT @curRow := 1 , @curNval := 3 ) r
order by item_id , price desc ) tab where item_rank in (1,2) ;
