mysql - 为特定的人选择最多 "popular"关注者。某人拥有的追随者越多，他们的 "popular"就越多

Question

Find most "popular" follower for a specific person. The more followers someone has, the more "popular" they are.

我需要 SQL 查询来选择特定人群中最受欢迎的关注者。

我的表 - (关注者)

id | person_id | follower_person_id
1    1            2
2    1            3
3    2            1
4    2            4
5    3            1
6    3            2
7    3            4
8    4            3

For example person_id 1 has total 2 follower, person_id 2 has total 2 followers, person_id 3 has total 3 followers and person_id 4 has total 2 followers.

Therefore, person_id 3 is most popular follower for person_id 1, person_id 1 is most popular follower for person_id 2 and so on...

这是我的查询，但它不起作用...

SELECT follower_person_id FROM followers f where f.person_id = 1 group by f.follower_person_id having max(select count(*) from followers where person_id = f.follower_person_id)

Answer 1

您可以使用以下查询来获取每个人的关注者数量:

SELECT person_id, COUNT(*) AS cnt
FROM followers
GROUP BY person_id 

输出:

person_id cnt
-------------
1         2
2         2
3         3
4         1

将上面的查询用作派生表，您可以获得每个人的关注者的关注者数量(听起来有点复杂!):

SELECT t1.person_id, t1.follower_person_id, t2.cnt
FROM followers AS t1
JOIN (
   SELECT person_id, COUNT(*) AS cnt
   FROM followers
   GROUP BY person_id 
) AS t2  ON t1.follower_person_id = t2.person_id

输出:

person_id, follower_person_id, cnt
------------------------------------
1,         2,                  2
1,         3,                  3
2,         1,                  2
2,         4,                  1
3,         1,                  2
3,         2,                  2
3,         4,                  1
4,         3,                  3

因为您只是在寻找一个特定的人，您可以在上述查询中使用WHERE 子句:

SELECT t1.person_id, t1.follower_person_id, t2.cnt
FROM followers AS t1
JOIN (
   SELECT person_id, COUNT(*) AS cnt
   FROM followers
   GROUP BY person_id 
) AS t2  ON t1.follower_person_id = t2.person_id
WHERE t1.person_id = 1
ORDER BY t2.cnt DESC LIMIT 1

ORDER BY 和 LIMIT 将为您提供特定人关注的最受欢迎的人。

输出:

person_id, follower_person_id, cnt
-----------------------------------
1,         3,                  3

注意:您可以使用变量概括第二个查询的输出，以获得每个人关注的最受欢迎的人(这是每组最大问题)。