mysql - SQL如何按关联计算项目

Question

表用户

ID   name   country
---  -----  -----
100  John    1
101  Mark    4
102  Peter   3
103  Lucy    3

餐 table 上的追随者

ID   folowerId  followedId 
---  ---------  ------
 1   102         101
 2   103         101
 3   102         100
 4   100         101
 5   100         102
 5   103         100

表国家

ID   name 
---  -----
1    China
2    Usa
3    Italia
4    India

如何获取按国家/地区分组的特定用户的关注者数量？

用户 101 的示例输出:

country   count
-------   -----
China      1 
Italia     2

我试过这个:

 SELECT 
     User.name, COUNT(Follower.id) AS FollowerCount 
 FROM 
    followers AS Follower 
    JOIN users AS User ON Follower.UserId = User.id 
 GROUP BY 
    User.country;

但是没用

Answer 1

你应该使用计数和分组依据

select c.name, count(*) 
from followers b 
left join users  as a on b.folloswerId = a.id
left join country on a.country = c.id
where b.followedId  = 101
group by c.name