返回所有用户的列表，与他们最“受欢迎"的用户配对.追随者.某人拥有的追随者越多，其“受欢迎"程度就越高.他们是 [英] Returns a list of all users, paired with their most "popular" follower. The more followers someone has, the more "popular" they are

问题描述

找到最受人们欢迎的追随者.某人的追随者越多， 他们是受欢迎的".

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

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

因此，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

此处是上述查询的说明

此查询仅适用于查找特定人物的受欢迎人物，但 我想为所有人找到一个与他们最受欢迎"的追随者配对.

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 table

id | name 
1    John         
2    Ali         
3    Rohn         
4    Veronica

现在我想将此ID转换为人名.

Now I want to convert this id to person name.

最终输出应该像

Final Output shoud like

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.

> 此处演示

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