Mysql 按两列分组并选择第三列的最大值

Question

我有一个表，其中包含 user_id、item_id 和交互类型作为列。交互类型可以是0、1、2、3、4或5。但是，对于某些user_id和item_id对，我们可能有多个interaction_type。例如，我们可能有:

user_id   item_id    interaction_type
2            3            1
2            3            0
2            3            5
4            1            0
5            4            4
5            4            2  

我想要的是仅在有多个时保留最大的interaction_type。所以我想要这个:

user_id   item_id    interaction_type
2            3            5
4            1            0
5            4            4

这是我为此目的编写的查询:

select user_id, item_id, max(interaction_type) as max_type
from mytable
group by user_id, item_id;

但是结果很奇怪。例如，在原始表中，我有 100000 行，interaction_type=5，但在结果表中，我只有 2000 行。这怎么可能，因为最大值将在包含 5 的每个比较之间选择 5，因此我不应该少于 5在结果表中。

Answer 1

您的查询没问题。您获得 2000 行的原因是因为您为每对唯一的值 user_id、item_id 获得一行。

如果您想查看每行的交互类型，请使用:

select user_id, item_id, max(interaction_type) as max_type,
       group_concat(distinct interaction_type) as interaction_types,
       count(*) as cnt
from mytable
group by user_id, item_id;

我突然想到您希望所有行都具有最大交互类型。如果是这样，计算最大值，然后查找与该值匹配的所有行:

select t.*
from mytable t cross join
     (select max(interaction_type) as maxit from mytable) x
     on x.maxit = t.interaction_type;

此查询不需要 group by。