选择最“受欢迎"的特定人的关注者.某人拥有的追随者越多，其“受欢迎"程度就越高.他们是

问题描述

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

I need SQL query to select most popular follower of particular people.

我的桌子-(关注者)

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

因此，person_id 3是person_id 1最受欢迎的关注者 person_id 1是person_id 2最受欢迎的关注者，依此类推...

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...

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

Here is my query but its not working...

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)

推荐答案

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

You can use the following query to get the number of followers for each person:

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

输出:

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

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

Using the above query as a derived table you can get the number of followers for follower for each person (sounds a bit complicated!):

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子句:

Since you are looking for just a specific person, you can use a WHERE clause in the above query:

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将为您提供特定人关注的最受欢迎的人.

ORDER BY with LIMIT will give you the most popular person the specific person is following.

输出:

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

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

Note: You can generalize the output of the second query using variables to get the most popular person each person follows (this is a greatest-per-group problem).

这篇关于选择最“受欢迎"的特定人的关注者.某人拥有的追随者越多，其“受欢迎"程度就越高.他们是的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！