mysql - 返回所有用户的列表，与他们最多的 "popular"关注者配对。某人拥有的追随者越多，他们的 "popular"就越多

Question

Find most "popular" follower for the persons. 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

Person_id 1 has total 2 follower (person_id 2, person_id 3), person_id 2 has total 2 followers (person_id 1, person_id 4), person_id 3 has total 3 followers (person_id 1, person_id 2, person_id 4)and person_id 4 has total 1 followers (person_id 3).

Therefore, person_id 3 is most popular follower for person_id 1, person_id 1 is most popular follower for person_id 2, person_id 1 (or person_id 2) is most popular follower for person_id 3 and person_id 3 is most popular for 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

this query only work for find popular person for a specific person but I want to find a paired with their most "popular" follower for all person.

SO输出应该是这样的

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

现在我有另一个person 表

id | name 
1    John         
2    Ali         
3    Rohn         
4    Veronica

现在我想将这个 id 转换为人名。

Final Output shoud like

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