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

select ui.uid,ei.exam_id,round(avg(score),0) avg_score from user_info ui,examination_info ei,exam_record er

where ui.uid = er.uid and ei.exam_id = er.exam_id

and (nick_name regexp '^牛客+[0-9]+号$'

or nick_name regexp '^[0-9]+')

and tag regexp '^c'

and submit_time is not null

group by ui.uid,ei.exam_id

order by ui.uid,avg(score);