返回所有用户的列表，并与他们最受欢迎的关注者配对。有人拥有的追随者越多，他们就越受欢迎

Question

寻找最受欢迎的追随者。有人拥有的追随者越多，他们就越“受欢迎”。返回所有用户的列表，并与他们最受欢迎的关注者配对。有人拥有的追随者越多，他们就越受欢迎

我需要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 

PERSON_ID 1具有总共2从动件（PERSON_ID 2，PERSON_ID 3），PERSON_ID 2已经总2个跟随者（PERSON_ID 1，PERSON_ID 4），PERSON_ID 3有 共有3名关注者（person_id 1，person_id 2，person_id 4）和person_id 4共有1位关注（person_id 3）。

因此，PERSON_ID 3是最流行的从动用于为person_id 1， PERSON_ID 1是最流行的跟随器用于为person_id 2，PERSON_ID 1（或 PERSON_ID 2）是最流行的跟随器用于为person_id 3和PERSON_ID 3 是最流行的为person_id 4.

这里查询...

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 

上面查询输出

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

Here is explanation of above query

这个查询只能用于寻找受欢迎的人对特定的人，但 我想找到对所有的人自己最“流行”的跟随者配对。

SO输出应该像

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

现在我有另一个person table

id | name 
1 John   
2 Ali   
3 Rohn   
4 Veronica 

现在我想给这个ID转换为人名。

最终输出768,16喜欢

person_name, follower_person_name, cnt 
-------------------------------------- 
John,  Rohn,     3 
Ali,   John,     2 
Rohn,  John,     2 
Veronica, Rohn,     3 

我需要SQL查询来获取这些数据。

Answer 1

您可以使用下面的查询：

SELECT person_name, follower_name, cnt 
FROM (
    SELECT person_name, follower_name, cnt, 
      @rn := IF(@pname = person_name, @rn + 1, 
        IF(@pname := person_name, 1, 1)) AS rn 
    FROM (
     SELECT t3.name AS person_name, t4.name AS follower_name, 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 
     JOIN person AS t3 ON t1.person_id = t3.id 
     JOIN person AS t4 ON t1.follower_person_id = t4.id 
    ) AS x 
    CROSS JOIN (SELECT @rn := 0, @pname := '') AS vars 
    ORDER BY person_name, cnt DESC) AS v 
wHERE v.rn = 1; 

输出：

person_name follower_name cnt 
-------------------------------- 
John  Rohn   3 
Veronica Rohn   3 
Ali   John   2 
Rohn  Ali    2 

查询使用变量，以获得最大的每组记录。

Demo here

Answer 2

SELECT p1.name, p2.name, 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 
join person p1 on t1.person_id=person.id 
join person p2 on t1.follower_person_id=person.id 
ORDER BY t2.cnt DESC LIMIT 1 

Answer 3

也许你可以在你选择的语言有一些代码结合起来 尝试下面的查询进行排序，从最高的追随者降低，让你的第二列的名称：

select f.person_id, p.name 
from followers f 
inner join person p on p.id=f.person_id 
group by f.person_id order by count(f.follower_person_id) desc 

对于每个输出ID（可以称之为FIXED_ID_FROM_PREVIOUS_QUERY你试图让他跟随者和创造怎么办Y列1和3

select p.name, count(f.follower_person_id) 
from followers f 
inner join person p on p.id=f.person_id 
inner join person pf on pf.id=f.follower_person_id where 
f.person_id in (select person_id from followers where follower_person_id=FIXED_ID_FROM_PREVIOUS_QUERY) 
group by f.person_id order by count(f.follower_person_id) desc