SQL39 筛选昵称规则和试卷规则的作答记录

题目主要信息:

• 找到昵称以 "牛客"+纯数字+"号" 或者纯数字组成的用户对于字母 c 开头的试卷类别（如C, C++, c#等）的已完成的试卷ID和平均得分
• 按用户ID、平均分升序排序
• 分数保留整数

问题拆分：

• 通过 WITH AS 语句筛选出符合要求的用户和试卷，并存入临时表供接下来使用。知识点：WITH AS, REGEXP, LIKE
  • 用正则表达式判断昵称是否合规，正则表达式中匹配单个纯数字符号为 \d，匹配一个及以上数字为 \d+，^ 表示匹配字符串头，$ 表示匹配字符串尾。因此 "牛客"+纯数字+"号" 的判断方式为 REGEXP "^牛客\d+号$"，纯数字组成的用户判断方式为 REGEXP "^\d+$"
  • 判断字母 c 开头的试卷类别条件较为简单，可以直接用 SQL 的字符串判断函数 LIKE "c%" 实现
• 筛选出 exam_record 中 uid 和 exam_id 满足上述条件的数据，对其进行聚合统计。通过 AVG 统计平均分数，并通过 ROUND 保留整数。知识点：AVG, ROUND, GROUP BY
• 对筛选结果按照 uid 和平均分数排序输出。知识点：ORDER BY

代码：

WITH
valid_u AS (SELECT uid FROM user_info WHERE nick_name REGEXP "^牛客\\d+号$" OR nick_name REGEXP "^\\d+$"),
valid_e AS (SELECT exam_id FROM examination_info WHERE tag LIKE "c%" OR tag LIKE "C%")
SELECT
    uid,
    exam_id,
    ROUND(AVG(score)) AS avg_score
FROM
    exam_record
WHERE
    uid IN (SELECT * FROM valid_u)
    AND exam_id IN (SELECT * FROM valid_e)
    AND score IS NOT NULL
GROUP BY
    uid,
    exam_id
ORDER BY
    uid,
    AVG(score)