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

WITH
    t1 AS (
-- 用户喜欢的音乐
        SELECT
            user_id,
            music_id,
            music_name
        FROM
            music_likes ml
            LEFT JOIN music m ON ml.music_id = m.id
    ),
    t2 AS (
        SELECT DISTINCT
            music_id,
            music_name
        FROM
            t1
        WHERE
            user_id IN (
-- 用户关注的用户id
                SELECT
                    follower_id
                FROM
                    follow
                WHERE
                    user_id = 1
            )
            AND music_name NOT IN (
 -- 用户1喜欢的音乐
                SELECT
                    music_name
                FROM
                    t1
                WHERE
                    user_id = 1
                    AND music_name IS NOT NULL
            )
    )
SELECT
    music_name
FROM
    t2
ORDER BY
    music_id;

关注distinct和order by使用的问题