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

with temp as (
    select follower_id from follow
    where user_id=1
),
temp1 as (
    SELECT DISTINCT music_id from music_likes ml
    join temp f on ml.user_id=f.follower_id
),
temp2 as (
    Select m.id as id, music_name from music m
    join temp1 t on t.music_id=m.id
),
temp3 as (
    select id, music_name,
           ROW_NUMBER() OVER (PARTITION BY music_name ORDER BY id) as rn
    from temp2 t2
    where NOT EXISTS (
        SELECT 1 from music_likes ml
        where user_id=1 AND ml.music_id = t2.id
    )
)
select music_name from temp3 
where rn = 1
order by id;