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

WITH t1 AS (
    SELECT *
    FROM music_likes
    WHERE user_id = 1
),
t2 AS (
    SELECT
           music_id,
           music_name
    FROM follow a
    LEFT JOIN music_likes b
    ON a.follower_id = b.user_id
    LEFT JOIN music c
    ON b.music_id = c.id
    WHERE a.user_id = 1
    AND music_id NOT IN (SELECT t1.music_id FROM t1)
    GROUP BY music_id, music_name
    ORDER BY music_id ASC

),
t3 AS (
    SELECT
        music_name
    FROM t2
)SELECT * FROM t3;

# 这道题的关键在于如何 not in 或者 not exist 语法