返回所有用户的列表，与他们最“受欢迎"的用户配对.追随者.某人拥有的追随者越多，其“受欢迎"程度就越高.他们是

问题描述

我需要SQL查询才能选择最受欢迎的关注者.

I need SQL query to select most popular follower of 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最受欢迎的关注者，person_id 1(或 person_id 2)是person_id 3和person_id 3最受欢迎的关注者 最受欢迎的是person_id 4.

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

此处是上述查询的说明

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转换为人名.

Now I want to convert this id to person name.

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

我需要sql查询来获取此数据.

I need sql query to get this data.

推荐答案

您可以使用以下查询:

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

查询使用变量以获取最大的每组记录.

The query uses variables in order to get the greatest-per-group record.

> 此处演示

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