题目：

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

个人抛出的问题：

题目不难，不过本人有个问题先抛出，对于找出“以『牛客』开头『号』结尾”的用户，我用的是LIKE，想知道是否有同学使用REGEXP来找出？欢迎指教！

解题思路

  • 我的解题思路是使用表联结，应该不是最直接明了的，看到有同学@logilong 使用了子查询（我这里也先把人家的答案贴上来），我觉得使用子查询更加便于阅读，可以先参考这个答案：
select uid, nick_name, achievement
from user_info
where nick_name like '牛客%号'
    and achievement between 1200 and 2500
    and uid in (
        select uid
        from (
            select uid, start_time as 'act_time'
            from exam_record
            union all
            select uid, submit_time as 'act_time'
            from practice_record
        ) as a
        group by uid
        having date_format(max(act_time), '%Y%m')='202109'
    )

步骤 1：先把用户的所有活动（答题和作答试卷）合并起来

  • 合并很简单了，就是使用UNION把exam_record和practice_record两个表合并起来，需要注意的是，合并的时候，两个表SELECT的字段的名称和数值类型必须一致
  • 另外，这里选取了submit_time这个字段，而非start_time，以submit_time作为判断用户是否活跃过
SELECT uid, exam_id AS activity_id, submit_time, 'exam' AS tag
FROM exam_record
UNION
SELECT uid, question_id AS activity_id, submit_time, 'question' AS tag
FROM practice_record

步骤 2：将步骤1的结果表左联结user_info表，再一次性找出：最近一次活跃时间在2021年9月，且昵称以『牛客』开头『号』结尾、成就值在1200~2500之间的用户

  • 这一步其实不难理解，但还是分开两个小步骤来讲：首先是左联结
SELECT t.* /* 不必在意这里select的字段，只是为了让查询语句能运行而已 */
FROM 
	/* 步骤1的结果 */
    (SELECT uid, exam_id AS activity_id, submit_time, 'exam' AS tag
    FROM exam_record
    UNION
    SELECT uid, question_id AS activity_id, submit_time, 'question' AS tag
    FROM practice_record) AS t
/* 重点在于下面的左联结 */
LEFT OUTER JOIN user_info AS ui
ON t.uid = ui.uid
  • 第二小步骤，找出最近一次活跃时间在2021年9月的用户，这里只需要查看只要看submit_time最大的时间值是否在2021年9月即可
  • 但是，group by字段是稍微不一样的，是根据uid、nick_name、achievement 三个字段来group by，而非单纯地依靠uid来分组，原因也很直接了当，因为最终的SELECT语句中的选取字段必须是GROUP BY中所含有的
  • 并且还有一个更重要的原因是（这也是为什么这个代码能运行且结果不出错的原因）：“uid - nick_name - achievement” 这三个字段都是一一对应且组合方式是唯一的；说人话就是，一个uid只会对应唯一一个nick_name和唯一一个achievement
  • 昵称以『牛客』开头『号』结尾、成就值在1200~2500之间的用户，只需通过WHERE来筛选即可，不需要使用GROUP BY
SELECT ui.uid, ui.nick_name, ui.achievement
FROM 
    (SELECT uid, exam_id AS activity_id, submit_time, 'exam' AS tag
    FROM exam_record
    UNION
    SELECT uid, question_id AS activity_id, submit_time, 'question' AS tag
    FROM practice_record) AS t

LEFT OUTER JOIN user_info AS ui
ON t.uid = ui.uid

/* 筛选出昵称以『牛客』开头『号』结尾、成就值在1200~2500之间的用户 */
WHERE ui.nick_name LIKE "牛客%号" AND ui.achievement BETWEEN 1200 AND 2500

/* 按照uid、nick_name、achievement三个字段来分组 */
GROUP BY ui.uid, ui.nick_name, ui.achievement

/* 选取年和月都在2021年9月的最大submit_time，进一步筛选出合适的用户 */
HAVING YEAR(MAX(t.submit_time)) = 2021 AND MONTH(MAX(t.submit_time)) = 9