返回用户最常用的链接ID

Question

我有三张桌子。user_sticker保存用户之间发送的所有贴纸。在加载配置文件视图屏幕时，我需要显示给用户的最常见的贴纸。

用户

• id_user
• 名字

贴纸

• id_sticker
• 名字

user_sticker

• id_user_sticker
• id_sticker
• id_user_from
• id_user_to

所以，如果user_sticker保存了这些信息：

• 1，3,254,205
• 2，2,362,205
• 3，2,519,205
• 4，3,945,205
• 5，3,199,205

(这意味着用户254,362,519,945,199向用户205发送贴纸)。结果必须返回相同记录中的、、用户205信息(名称)和最常见的粘贴id (在本例中为#3)。

Answer 1

好吧，让我看看

在原则上，您需要使用user_sticker表。所以：

SELECT *
FROM user_sticker us

然后，与user_to的user表匹配

SELECT *
FROM user_sticker us JOIN user u ON us.id_user_to = u.id_user

然后，你需要你想要的用户的名字和贴纸。

SELECT us.id_sticker, u.name
FROM user_sticker us JOIN user u ON us.id_user_to = u.id_user
WHERE u.id_user = "yourwanteduser"

现在，选第一个

SELECT TOP 1 us.id_sticker, u.name
FROM user_sticker us JOIN user u ON us.id_user_to = u.id_user
WHERE u.id_user = "yourwanteduser"
ORDER BY (SELECT COUNT(*)
          FROM user_sticker us2
          WHERE us2.id_user_to=u.id_user AND us2.id_sticker=us.id_sticker)

ORDER BY用于搜索有多少贴纸已发送给该用户。

对不起我的英语不好！我希望这能帮上忙！

Answer 2

这将是选择：

   select id_user_to, count(id_sticker) quant from user_sticker group by id_sticker order by quant desc limit 1;

现在使用用户的信息: JOIN

select us.id_user_to, u.name, count(us.id_sticker) quant 
from user_sticker us
join user u on u.id_user=us.id_user_to
group by us.id_sticker 
order by quant desc limit 1;

Answer 3

select test.id_sticker, test.id_user_to, name from 
(select s.id_sticker, s.id_user_to
from user_sticker s, user u
group by id_sticker, id_user_to
order by count(*) desc
limit 1) 
as test, user
where test.id_user_to=user.id_user

请参阅sql花键链接：http://sqlfiddle.com/#!2/e02ca/6/0