跳到主要内容

SQL292. 网易云音乐推荐（网易校招笔试真题）

题目描述​

查询向 user_id = 1 的用户，推荐其关注的人喜欢的音乐。

要求：

  • 不要推荐该用户已经喜欢的音乐
  • 按 music_id 升序排列
  • 结果中不应当包含重复项

输出：music_name（音乐名称）

表结构​

表1：follow（关注关系表）​

CREATE TABLE follow (
    user_id     INT(4) COMMENT '关注人的id',
    follower_id INT(4) COMMENT '被关注人的id',
    PRIMARY KEY (user_id, follower_id)
);
字段名类型说明
user_idINT(4)关注人的id
follower_idINT(4)被关注人的id

语义：user_id 关注了 follower_id。

表2：music_likes（用户喜欢的音乐）​

CREATE TABLE music_likes (
    user_id  INT(4) COMMENT '用户id',
    music_id INT(4) COMMENT '喜欢的音乐id',
    PRIMARY KEY (user_id, music_id)
);
字段名类型说明
user_idINT(4)用户id
music_idINT(4)喜欢的音乐id

表3：music（音乐信息表）​

CREATE TABLE music (
    id         INT(4)      PRIMARY KEY COMMENT '音乐id',
    music_name VARCHAR(32)             COMMENT '音乐名称'
);
字段名类型说明
idINT(4)音乐id（主键）
music_nameVARCHAR(32)音乐名称

示例数据​

follow​

user_idfollower_id
12
14
23

music_likes​

user_idmusic_id
117
218
219
320
417

music​

idmusic_name
17yueyawang
18kong
19MOM
20Sold Out

预期输出​

music_name
kong
MOM

解释：

  • 用户 1 关注用户 2 和 4
  • 用户 2 喜欢音乐 18（kong）、19（MOM）
  • 用户 4 喜欢音乐 17（yueyawang）
  • 用户 1 自己已经喜欢音乐 17
  • 排除用户 1 已喜欢的 17，剩余 18（kong）、19（MOM）
  • 按 music_id 升序输出：kong、MOM

解题思路​

第一步：理解需求​

这是一个典型的"社交推荐"场景：给用户推荐其好友喜欢的东西，但排除用户已经拥有的。需要将三张表的信息串联起来。

第二步：分析表结构​

三张表形成一条链：

  • follow：谁关注了谁（找到用户1关注的人）
  • music_likes：谁喜欢什么音乐（找到被关注的人喜欢的音乐）
  • music：音乐的名称（将音乐id映射为名称）

第三步：确定 SQL 方案​

  1. 从 follow 表中找出 user_id = 1 关注的所有人（follower_id）
  2. 通过 music_likes 表找出这些人喜欢的 music_id
  3. 排除用户 1 自己已经喜欢的 music_id
  4. 通过 music 表获取音乐名称
  5. 用 DISTINCT 去重，ORDER BY 排序

第四步：编写并验证 SQL​

核心是三表 JOIN + NOT IN / NOT EXISTS 排除。

完整 SQL 实现​

-- 方法一：NOT IN + DISTINCT —— 清晰直观
SELECT DISTINCT
    m.music_name
FROM follow AS f
JOIN music_likes AS ml
    ON f.follower_id = ml.user_id
JOIN music AS m
    ON ml.music_id = m.id
WHERE f.user_id = 1
  AND ml.music_id NOT IN (
      SELECT music_id FROM music_likes WHERE user_id = 1
  )
ORDER BY m.id ASC;

-- 方法二：LEFT JOIN + IS NULL —— 避免 NOT IN 的 NULL 陷阱
SELECT DISTINCT
    m.music_name
FROM follow AS f
JOIN music_likes AS ml
    ON f.follower_id = ml.user_id
JOIN music AS m
    ON ml.music_id = m.id
LEFT JOIN music_likes AS my_ml
    ON my_ml.user_id = f.user_id
    AND my_ml.music_id = ml.music_id
WHERE f.user_id = 1
  AND my_ml.music_id IS NULL
ORDER BY m.id ASC;

-- 方法三：NOT EXISTS —— 大数据量时性能更优
SELECT DISTINCT
    m.music_name
FROM follow AS f
JOIN music_likes AS ml
    ON f.follower_id = ml.user_id
JOIN music AS m
    ON ml.music_id = m.id
WHERE f.user_id = 1
  AND NOT EXISTS (
      SELECT 1
      FROM music_likes AS my_ml
      WHERE my_ml.user_id = f.user_id
        AND my_ml.music_id = ml.music_id
  )
ORDER BY m.id ASC;

解法详解​

数据流图解​

user_id=1
    │
    ▼
follow: (1,2), (1,4)  →  follower_id = {2, 4}
    │
    ▼
music_likes: 2→{18,19}, 4→{17}  →  music_id = {18, 19, 17}
    │
    ▼
排除 user=1 已喜欢的: 1→{17}  →  music_id = {18, 19}
    │
    ▼
music: 18→kong, 19→MOM  →  输出: kong, MOM

方法一：NOT IN（最直观）​

SELECT DISTINCT m.music_name
FROM follow AS f
JOIN music_likes AS ml ON f.follower_id = ml.user_id
JOIN music AS m ON ml.music_id = m.id
WHERE f.user_id = 1
  AND ml.music_id NOT IN (
      SELECT music_id FROM music_likes WHERE user_id = 1
  )
ORDER BY m.id ASC;

执行过程：

  1. JOIN 后得到用户 1 关注的人喜欢的所有音乐：{18, 19, 17}
  2. 子查询得到用户 1 已喜欢的音乐：{17}
  3. NOT IN 排除 17，剩余 {18, 19}
  4. DISTINCT 去重（本题无重复，但保险起见）
  5. ORDER BY m.id ASC 按音乐id升序：18 → 19

方法二：LEFT JOIN + IS NULL​

SELECT DISTINCT m.music_name
FROM follow AS f
JOIN music_likes AS ml ON f.follower_id = ml.user_id
JOIN music AS m ON ml.music_id = m.id
LEFT JOIN music_likes AS my_ml
    ON my_ml.user_id = f.user_id
    AND my_ml.music_id = ml.music_id
WHERE f.user_id = 1 AND my_ml.music_id IS NULL;

关键点：LEFT JOIN 的条件必须同时包含 user_id 和 music_id，确保只排除"该用户已喜欢的特定音乐"，而不是排除所有该用户喜欢的音乐。

方法三：NOT EXISTS（推荐用于大数据量）​

SELECT DISTINCT m.music_name
FROM follow AS f
JOIN music_likes AS ml ON f.follower_id = ml.user_id
JOIN music AS m ON ml.music_id = m.id
WHERE f.user_id = 1
  AND NOT EXISTS (
      SELECT 1 FROM music_likes AS my_ml
      WHERE my_ml.user_id = f.user_id
        AND my_ml.music_id = ml.music_id
  );

NOT EXISTS 的优势：

  • 不受 NULL 值影响
  • 可以"短路"——找到第一个匹配就停止扫描
  • 大数据量时通常性能最好

知识点总结​

1. 三表 JOIN​

SELECT *
FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.c_id = C.id;

多表 JOIN 的执行顺序通常是从左到右，数据库优化器会选择最优的连接顺序。

2. NOT IN vs NOT EXISTS​

特性NOT INNOT EXISTS
NULL 处理子查询含 NULL 则返回空结果不受 NULL 影响
性能（大数据量）较慢，需完整扫描子查询较快，可提前终止
可读性更直观稍复杂

最佳实践：除非确定子查询结果无 NULL，否则优先使用 NOT EXISTS。

3. DISTINCT 去重​

SELECT DISTINCT column FROM table;

DISTINCT 对结果集去重。本题中如果用户 2 和用户 4 都喜欢同一首歌，没有 DISTINCT 会输出重复的音乐名。

易错点总结​

1. 搞反 follow 表的语义​

-- 错误！把 user_id 和 follower_id 搞反了
WHERE f.follower_id = 1  -- 这是"被用户1关注的人"，不是"用户1关注的人"

正确理解：user_id 是关注者，follower_id 是被关注者。用户 1 关注的人应该是 user_id = 1。

2. NOT IN 遇到 NULL​

-- 危险！如果 music_likes 表中有 user_id=1 且 music_id=NULL 的记录
SELECT ... WHERE ml.music_id NOT IN (
    SELECT music_id FROM music_likes WHERE user_id = 1
);
-- 子查询返回 {17, NULL}，NOT IN 遇到 NULL 会导致整个查询返回空结果

安全做法：子查询中加上 AND music_id IS NOT NULL。

3. LEFT JOIN 条件写错​

-- 错误！条件不完整
LEFT JOIN music_likes AS my_ml ON my_ml.music_id = ml.music_id
-- 这样会把"任何人喜欢的这首歌"都排除，不只是 user_id=1

正确条件：ON my_ml.user_id = f.user_id AND my_ml.music_id = ml.music_id

4. 忘记去重​

如果用户 2 和用户 4 都喜欢同一首歌（比如都喜欢了音乐 18），没有 DISTINCT 会输出两个 "kong"。

5. 排序条件错误​

题目要求按 music_id 升序，不是按 music_name 字母顺序。注意：

ORDER BY m.id ASC      -- 正确，按音乐id排序
ORDER BY m.music_name  -- 错误，按名称字母排序

扩展思考​

  • 如果要求推荐"关注的人喜欢的音乐的歌手"？ 需要引入 artist 表，多一层 JOIN。
  • 如果要求按喜欢人数降序推荐？ 用 COUNT(*) 分组，再 ORDER BY count DESC。
  • 如果要求随机推荐 5 首？ 用 ORDER BY RAND() LIMIT 5（MySQL）。
  • 性能考虑：在 follow.user_id、music_likes.user_id、music_likes.music_id 上建立索引。

相关题目​

加载评论中...