1264页面推荐（中等 union all ）稍微的难点在于找1的朋友

最新推荐文章于 2022-04-27 13:41:13 发布

每天都要被自己菜醒

最新推荐文章于 2022-04-27 13:41:13 发布

阅读量125

点赞数

分类专栏： leetcode 数据库刷题文章标签： mysql

本文链接：https://blog.csdn.net/qq_45531594/article/details/108413343

版权

数据库刷题同时被 2 个专栏收录

38 篇文章 3 订阅

订阅专栏

leetcode

21 篇文章 0 订阅

订阅专栏

Create table If Not Exists Friendship (user1_id int, user2_id int)
Create table If Not Exists Likes (user_id int, page_id int)
Truncate table Friendship
insert into Friendship (user1_id, user2_id) values ('1', '2')
insert into Friendship (user1_id, user2_id) values ('1', '3')
insert into Friendship (user1_id, user2_id) values ('1', '4')
insert into Friendship (user1_id, user2_id) values ('2', '3')
insert into Friendship (user1_id, user2_id) values ('2', '4')
insert into Friendship (user1_id, user2_id) values ('2', '5')
insert into Friendship (user1_id, user2_id) values ('6', '1')
Truncate table Likes
insert into Likes (user_id, page_id) values ('1', '88')
insert into Likes (user_id, page_id) values ('2', '23')
insert into Likes (user_id, page_id) values ('3', '24')
insert into Likes (user_id, page_id) values ('4', '56')
insert into Likes (user_id, page_id) values ('5', '11')
insert into Likes (user_id, page_id) values ('6', '33')
insert into Likes (user_id, page_id) values ('2', '77')
insert into Likes (user_id, page_id) values ('3', '77')
insert into Likes (user_id, page_id) values ('6', '88')

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200904233840843.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1NTMxNTk0,size_16,color_FFFFFF,t_70#pic_center

在这里插入图片描述


select   distinct page_id  as 'recommended_page'
from 
    #找到用户为 1 的所有朋友
    (select  user1_id,user2_id
    from Friendship
    where user1_id=1 
    union all 
    select  user2_id,user1_id
    from Friendship
    where user2_id=1 
) a     
#找到朋友的喜欢
join Likes  b on a.user2_id =  b. user_id 
# 不在1 的喜欢之中
where page_id not in 
(
    select page_id
    from Likes 
    where user_id=1
)