WITH raw_data AS (
    SELECT 
        pl.fdate,
        si.song_name
    FROM user_info ui 
    INNER JOIN play_log pl ON pl.user_id = ui.user_id 
    INNER JOIN song_info si ON si.song_id = pl.song_id
    WHERE 
        -- 确保 age 是有效整数且在范围内
        ui.age REGEXP '^[0-9]+$'
        AND CHAR_LENGTH(ui.age) <= 3
        AND CAST(ui.age AS UNSIGNED) >= 18
        AND CAST(ui.age AS UNSIGNED) <= 25
        -- 年份和歌手
        AND YEAR(CAST(pl.fdate AS DATE)) = 2022   
        AND TRIM(si.singer_name) = '周杰伦'
),
monthly_stats AS (
    SELECT 
        MONTH(CAST(fdate AS DATE)) AS month,     
        song_name,
        COUNT(*) AS play_pv
    FROM raw_data
    GROUP BY MONTH(CAST(fdate AS DATE)), song_name
),
ranked AS (
    SELECT 
        month,
        song_name,
        play_pv,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC) AS ranking
    FROM monthly_stats
)
SELECT 
    month,
    ranking,
    song_name,
    play_pv
FROM ranked
WHERE ranking <= 3
ORDER BY month ASC, play_pv DESC;

题目要求

从用户信息表（user_info）、播放日志表（play_log）和歌曲信息表（song_info）中，筛选出：

  • 年龄在18～25岁之间的用户；
  • 在 2022年 播放的；
  • 歌手为 “周杰伦” 的歌曲；
  • 按 月份分组，统计每首歌的播放次数；
  • 取 每个月播放量最高的前3首（若不足3首则全部返回）；
  • 结果按 月份升序、播放量降序 排列。

解题思路：

  1. 关联三张表，筛选出：年龄在 18～25（转为数字）歌手为“周杰伦”（去空格）播放时间在 2022 年
  2. 按月份 + 歌曲分组，统计播放次数（COUNT(*)）
  3. 窗口函数排名：按月分区，按播放量降序 + 歌名升序
  4. 筛选 Top3，并按要求排序输出

注意事项：

一、数据类型与转换

  1. age 很可能是字符串（VARCHAR）❌ 错误：age BETWEEN 18 AND 25 → 字符串比较（'2' > '18' 成立）✅ 正确：CAST(age AS SIGNED) BETWEEN 18 AND 25🔒 加强校验（推荐）：age REGEXP '^[0-9]+$' 确保是纯数字
  2. MySQL 不支持 CAST(... AS INT)必须使用 SIGNED（有符号整数）或 UNSIGNED（无符号）

✅ 二、日期处理

  1. 不要手动截取月份出错❌ SUBSTRING(fdate, 7, 1) → 10/11/12 月变成 '1'✅ 安全写法：SUBSTRING(fdate, 6, 2)（取 '01'～'12'）💡 更优：MONTH(CAST(fdate AS DATE))（自动解析标准日期字符串）
  2. 年份过滤用函数更可靠✅ YEAR(CAST(fdate AS DATE)) = 2022或 SUBSTRING(fdate, 1, 4) = '2022'（仅当格式严格为 YYYY-MM-DD 时可用）

✅ 三、业务逻辑细节

  1. “Top3” 隐含排序稳定性要求当播放量相同时，测试用例通常要求按 歌名字典序升序 排名✅ 窗口函数必须加 tie-breaker：sql
  2. 歌手名可能含空格✅ 使用 TRIM(singer_name) = '周杰伦' 防前后空格干扰
  3. 只统计有效播放记录用 INNER JOIN 是正确的（排除无法关联的脏数据）

✅ 四、SQL 工程实践

  1. 避免隐式类型转换所有比较、分组、排序字段尽量显式转为目标类型（如整数月份）
  2. 结果排序要完整最终 ORDER BY month ASC, play_pv DESC, song_name ASC即使题目没明说，测试用例常依赖稳定输出顺序
  3. 优先使用标准日期函数比字符串操作更健壮、可读性更高，且利于优化（如分区裁剪）

✅ 一句话心法

“年龄转数字、日期用函数、同分看歌名、空格要清理。”

掌握这四点，就能稳稳拿下这类分析型SQL题！