题目描述:
写出一个查询语句,找到具有最多共同关注者的所有两两结对组。换句话说,如果有两个用户的共同关注者是最大的,我们应该返回所有具有此最大值的两两结对组
The result table should contain the pairs user1_id and user2_id where user1_id < user2_id.
结果返回表,每一行应该包含user1_id和 user2_id,其中user1_id < user2_id.
返回结果 不要求顺序 。
查询结果格式如下例:
方法1:
主要思路:解题链接汇总
select user1_id ,user2_id
from (
select r1.user_id as user1_id, r2.user_id as user2_id ,rank() over(order by count(*) desc) as rnk
from Relations as r1 ,Relations as r2
where r1.user_id<r2.user_id and r1.follower_id=r2.follower_id
group by r1.user_id,r2.user_id
) tmp
where rnk=1