坚定的友谊sql

最新推荐文章于 2024-08-30 14:08:50 发布

什么阳

最新推荐文章于 2024-08-30 14:08:50 发布

阅读量36

点赞数

分类专栏： sql训练文章标签： sql mybatis 数据库

原文链接：https://leetcode.cn/problems/strong-friendship/solution/100-by-fei-wu-gai-zao-ri-ji-es0s/

版权

sql训练专栏收录该内容

24 篇文章 0 订阅

订阅专栏

with t as(
select user2_id user1_id,user1_id user2_id
from friendship
union all
select user1_id user1_id,user2_id user2_id
from friendship
)

select distinct t1.user1_id, t2.user1_id as user2_id,count(*)as common_friend from
t t1 inner join t t2 on t1.user2_id=t2.user2_id and t1.user1_id!=t2.user1_id and t1.user1_id<t2.user1_id
where (t1.user1_id,t2.user1_id)in
(
    select *from t
)
group by (t1.user1_id,t2.user1_id)
having count(*)>=3