SQL38 筛选限定昵称成就值活跃日期的用户

题目主要信息：

• 找到昵称以『牛客』开头『号』结尾、成就值在 1200~2500 之间，且最近一次活跃（答题或作答试卷）在 2021 年 9 月的用户信息
• 输出 uid, nick_name, achievement 字段

问题拆解：

• 通过 WITH AS 语句从多个表中筛选符合要求的用户信息。知识点：LIKE, BETWEEN, AND, UNION, MAX
  • nick_name LIKE "牛客%号" 筛选出昵称以『牛客』开头『号』结尾的用户
  • achievement BETWEEN 1200 AND 2500 筛选出成就值在 1200~2500 之间的用户，通过 AND 逻辑符号和第一条筛选规则连接
  • 通过 MAX(DATE_FORMAT(start_time ,"%Y%m"))="202109" 分别从 exam_record 和 prectice_record 中筛选出最后一次活跃在 2021 年 9 月的用户，使用 UNION 将二者拼接
• 将上述两个临时表 JOIN 起来，输出题目所需的字段。知识点：JOIN

代码：

WITH
valid_u AS (SELECT * FROM user_info WHERE nick_name LIKE "牛客%号" AND achievement BETWEEN 1200 AND 2500),
valid_e AS (SELECT uid FROM exam_record GROUP BY uid HAVING MAX(DATE_FORMAT(start_time ,"%Y%m"))="202109"),
valid_p AS (SELECT uid FROM practice_record GROUP BY uid HAVING MAX(DATE_FORMAT(submit_time ,"%Y%m"))="202109")
SELECT
    valid_u.uid,
    nick_name,
    achievement
FROM
    valid_u
JOIN
    (SELECT * FROM valid_e UNION SELECT * FROM valid_p) AS valid_uid
ON valid_u.uid = valid_uid.uid