题解 | #筛选昵称规则和试卷规则的作答记录#

select er.uid
     , er.exam_id
     , round(avg(score)) avg_score
from exam_record er
         inner join user_info ui on er.uid = ui.uid and ui.uid in (select uid
                                                                   from user_info
                                                                   where nick_name regexp '^牛客[1-9]+号$'
                                                                      or nick_name regexp '^[1-9]+$')
         inner join examination_info ei on er.exam_id = ei.exam_id and ei.exam_id in (select exam_id
                                                                                      from examination_info
                                                                                      where tag regexp '^c')
where score is not null
group by er.uid, er.exam_id
order by er.uid, avg_score;