题解 | 网易云音乐推荐(网易校招笔试真题)

# 请你编写一个SQL，查询向user_id = 1的用户，推荐其关注的人喜欢的音乐。
# 不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项。
with s1 as
(
select 
    user_id,
    music_id,
    music_name
from 
    music_likes left join music 
    on music_id = id
),
s2 as
(
select
    user_id,
    music_id,
    music_name
from s1
where user_id in (select follower_id from follow f where f.user_id = 1)
),
s3 as
(
select
    user_id,
    music_id,
    music_name
from s1
where user_id = 1
)

select 
    music_name 
from
(
select distinct music_id,music_name from s1
where 
    music_id in (select music_id from s2)
    and
    music_id not in (select music_id from s3)
) as temp
order by music_id
;