一步一步拆解问题,最后组合起来。
- 查询user_id = 1 的用户,其关注的人(follower_id )
select follower_id from follow where user_id = 1;
- 查询user_id = 1 的用户,其关注的人喜欢的音乐 (music_id)
select music_id from music_likes where user_id in (select follower_id from follow where user_id = 1);
- 查询user_id = 1 的用户,其关注的人喜欢的音乐,同时排除该用户已经喜欢的音乐(music_id)
select music_id from music_likes 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);
- 查询user_id = 1 的用户,其关注的人喜欢的音乐,同时排除该用户已经喜欢的音乐(music_id)。接着,连接表music,获得音乐name,并且按music的id升序排列,返回的结果不包含重复项。即最终的代码为:
select distinct music_name from ( select music_id from music_likes 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) ) a join music m on a.music_id = m.id order by id ;