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

WITH
    t AS (
        SELECT
            a.music_id,
            b.music_name
        FROM
            (
                SELECT DISTINCT
                    music_id
                FROM
                    music_likes
                WHERE
                    user_id IN (
                        SELECT
                            follower_id
                        FROM
                            follow
                        WHERE
                            user_id = 1
                    )
                    OR user_id = 1
            ) AS a
            JOIN music AS b ON a.music_id = b.id
        WHERE
            a.music_id NOT IN(
                SELECT
                    music_id
                FROM
                    music_likes
                WHERE
                    user_id = 1
            )
    )
SELECT
    music_name
FROM
    t
ORDER BY
    music_id ASC

先选出用户自己和他关注的人的喜欢的音乐然后去重再去掉用户自己喜欢的音乐，再和music表连接后排序就好了,感觉自己写的比较麻烦但是懒得改了嘿嘿