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

select
  t1.uid,
  t1.exam_id,
  round(avg(t1.score), 0) as avg_score
from exam_record as t1
  left join examination_info as t2 on t1.exam_id = t2.exam_id
  left join user_info as t3 on t1.uid = t3.uid
where 
  t1.score is not null
  and (t3.nick_name rlike '^牛客[0-9]{1,}号$'
  or t3.nick_name rlike '^[0-9]{1,}$')
  and t2.tag rlike '^[c, C].{0,}'
group by t1.uid, t1.exam_id
order by t1.uid, avg_score;