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

# 找到user_id = 1关注的用户
with t1 as (
select follower_id
from follow
where user_id = 1
),
# 找到他关注的用户喜欢的音乐
t2 as (
select t1.follower_id, a.music_id
from t1
left join music_likes as a
on t1.follower_id = a.user_id
),
t3 as (
select t2.*, b.music_name
from t2
left join music as b
on t2.music_id = b.id
),
# 找到user_id = 1的人的喜欢音乐
t4 as (
    select music_name
    from (
        select c.user_id, d.music_name
        from music_likes as c
        left join music as d
        on d.id = c.music_id
    ) as e
    where user_id = 1
)
select music_name
from (
    select DISTINCT music_name, music_id
    from t3
    where music_name not in (
                select music_name
                from t4
                )
    order by music_id
) as f;