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

select exam_record.uid,exam_record.exam_id,round(avg(score),0) as avg_score
from exam_record
left join
examination_info
using(exam_id)
left join
user_info
using(uid)
where (user_info.nick_name rlike '^牛客[0-9]+号$' or user_info.nick_name rlike '^[0-9]+$')
and
examination_info.tag rlike '(C|c).*'
and score is not null
group by exam_record.uid,exam_record.exam_id #聚合条件 首先根据用户再根据试卷ID
order by exam_record.uid,avg_score