选择多个最大值

Question

我有一个名为 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

此查询返回的某些行的 user_id 不正确。但是，结果集中的所有行都显示每个商品的正确最高价格。

Answer 1

您可能想要使用派生表，如下所示：

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;

测试用例：

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);

结果：

+---------+-----------+-------------------+
| 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)

Answer 2

也许这有点长，但你会提高可读性

SELECT
        *
FROM
    `order`
JOIN
    (
        SELECT 
            item_id, 
            MAX(price) price 
        FROM 
            `order` 
        GROUP BY 
            item_id
    ) 
    USING(item_id, price);

Answer 3

您需要首先获取每个商品 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;

Answer 4

这是一个每组最大问题。对于这个常见问题，有各种方法。在 MySQL 上，使用空自连接通常比任何涉及子查询的方法更快、更简单：

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

即。 “选择价格较高的同一商品不存在其他行的每一行”。

（如果两行具有相同的最高价格，您将同时返回。在平局的情况下到底该怎么做是每组最大解决方案的普遍问题。）

Answer 5

SELECT user_id, item_id, MAX(price) 
FROM order
GROUP BY item_id

您使用的 SQL 与 GROUP 相矛盾。
一旦使用GROUP，MYSQL总是会选择第一个user_id，但是最高的价格，这就是为什么用户错了但价格对的原因。

您可以尝试添加 ORDER BY Price DESC 来看看会发生什么，但我没有在我的环境中尝试。

Answer 6

您的查询按 item_id 对行进行分组。如果您有多个 item_id 1 的项目，且具有不同的 user_id，则只会选择第一个 user_id，而不是 user_id< /code> 价格最高。

Answer 7

您需要按 item_id 和 user_id 进行分组（显示每个用户每件商品的最高价格），或者如果您只想要组中的商品，则需要重新考虑 user_id 列。
例如，显示商品的最高价格并显示最后更改价格的用户，或者显示商品的最高价格并显示制定该商品最高价格的用户等。请查看 这篇文章的一些模式这。

Answer 8

如果您想要订单中的前 2 个，请尝试此...

如果您想要前 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) ;