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

题目主要信息：

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

问题拆分：

• 要求得满足条件的每位用户的每份试卷平均分，即以uid和exam_id进行分组。知识点：group by
• 筛选过滤每组，计算每组平均分：
  • 首先试卷标签、用户昵称、得分分布在三个表中，我们要以exam_id将表exam_record和表examination_info连接起来，然后再通过uid连接上user_info。知识点：join...on...
  • 筛选条件：知识点：where
    • 这份试卷必须有得分，则score不能为null。
    • 试卷标签开头是字母C或者c，用正则表达式匹配开头。tag rlike '^(C|c).*' 知识点：rlike
    • 用正则表达式匹配全数字的昵称或者以"牛客"+纯数字+"号"的昵称。nick_name rlike '^[0-9]+$' or nick_name rlike '^牛客[0-9]+号$' 知识点：rlike、or
  • 对筛选出来的每组结果求得分平均值，保留整数。round(avg(score), 0) as avg_score 知识点：round、avg
• 按照先用户ID后平均分升序排序。知识点：order by

代码：

select u_i.uid as uid, 
       e_r.exam_id as exam_id,
       round(avg(score), 0) as avg_score
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
join user_info u_i
on e_r.uid = u_i.uid
where score is not null 
and tag rlike '^(C|c).*'
and (nick_name rlike '^[0-9]+$'
    or nick_name rlike '^牛客[0-9]+号$' 
)
group by uid, exam_id
order by uid, avg_score