题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/second-degree-follower
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
本人思路:
1、先分组找到被关注者(followee)有多少去重的粉丝(follower),得到临时表tmp
2、要求follwee也在follower中,我用条件:follow表的follwer和tmp表的followee相等 进行的内连接,从而实现该要求,注意follwee要去重。
3、步骤2的实现也可以用where followee in (tmp)使用列级子查询
select distinct f1.follower as follower ,tmp.follower_num as num
from follow as f1 inner join
(select followee,count(distinct follower) as follower_num
from follow
group by followee) as tmp
on f1.follower = tmp.followee
order by f1.follower