SELECT person_id, COUNT(*) AS cnt
FROM followers
GROUP BY person_id
person_id cnt
-------------
1 2
2 2
3 3
4 1
使用上面的查询作为派生表,您可以获得每个人的关注者的关注者数量(听起来有点复杂!):
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
在上面的查询中使用一个子句:
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
会给你具体的人是继最受欢迎的人。
person_id, follower_person_id, cnt
-----------------------------------
1, 3, 3