看到题解里好多这部分的题的答案无法通过,分享一下自己的解题思路。
一、筛选出user_id为1用户关注的用户id
where user_id in(select follower_id from follow where user_id = 1)
二、筛选出user_id为1的用户喜欢的音乐id
and music_id not in(select music_id from music_likes where user_id = 1)
三、 左连接music表
select b.id,b.music_name from music_likes as a left join music as b on a.music_id = b.id where user_id in(select follower_id from follow where user_id = 1) and music_id not in(select music_id from music_likes where user_id = 1) order by b.id
四、 用group by 代替distinct 去重
注:mysql不能将distinct和order by进行连用,所以这里我用group by 进行去重,发现是可以通过的。完整代码如下:select music_name from ( select b.id,b.music_name from music_likes as a left join music as b on a.music_id = b.id where user_id in(select follower_id from follow where user_id = 1) and music_id not in(select music_id from music_likes where user_id = 1) order by b.id ) as t1 group by id order by id