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

-- 1.id=1的用户喜欢的音乐 
with id_1_like as (
    select m.*
    from music m
    join music_likes ml on m.id = ml.music_id
    where ml.user_id ='1'
),
--2. 用户关注的人 --->喜欢的音乐id去重
    id_1_follow as(
        select follower_id
        from follow
        where user_id='1'
    ),
-- 用户关注的人喜欢的音乐id 去重并排序
    id1follow_like as (
        select distinct m.id,
                        m.music_name
        from music m
        join music_likes ml on m.id = ml.music_id
        where ml.user_id in (select * from id_1_follow)
        order by m.id
    )
-- 3.答案=第二步-第一步
select distinct il.music_name
from id1follow_like il
where il.id not in(
    select i1l.id from id_1_like i1l
    )
;