在其他表格中选择max with group by

I have 2 tables: users_item that has 2 columns user_id, item_id and item_rates that has 2 columns rate_item_id, rate.

我有2个表:users_item有2列user_id,item_id和item_rates有2列rate_item_id,rate。

They are connected with Foreign_Key on users_item.item_id = item_rates.rate_item_id. I need to select item_id's with max rate for a given range of users. One user can have a lot of items. My select is:

它们与users_item.item_id = item_rates.rate_item_id上的Foreign_Key相关联。我需要为给定范围的用户选择具有最大费率的item_id。一个用户可以拥有很多物品。我的选择是:

SELECT MAX(rate), rate_item_id, user_id 
FROM users_item JOIN item_rates ON item_id = rate_item_id  
AND user_id in (2706,2979) GROUP BY user_id;

but it returns not correspondent item_id's with max rate. In given example select has to return just 2 rows. Can someone help on this. Thanks in advance.

但它返回的不是对应的item_id的最大速率。在给定的示例中,select必须仅返回2行。有人可以帮忙吗?提前致谢。

2 个解决方案

#1

Ok, I found what you want.. Try this:

好的,我找到了你想要的东西..试试这个:

SELECT users_item.user_id, item_id, maxrate
FROM user_items 
JOIN item_rates ON users_item.item_id = item_rates.rate_item_id
JOIN (SELECT MAX(rate) AS maxrate, user_id 
      FROM users_item JOIN item_rates ON item_id = rate_item_id
      WHERE user_id in (1,2)
      GROUP BY user_id) AS maxis
ON users_item.USER_ID = maxis.USER_ID
WHERE item_rates.rate = maxrate

The reason you need a subquery is that multiple different items own by the same user could have the same rate and this could be the maximum rate of user's owned items.

您需要子查询的原因是同一用户拥有的多个不同项可能具有相同的速率,这可能是用户拥有的项的最大速率。

#2

Try grouping by user_id, rate_item_id

尝试按user_id,rate_item_id进行分组

I'm surprised that MySql doesn't give you an error, Oracle would...

我很惊讶MySql不会给你一个错误,Oracle会......

#1