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

select 
a.uid,
a.exam_id,
format(avg(a.score),0) avg_score
from 
exam_record a
left join user_info b 
on a.uid=b.uid
left join examination_info c
on a.exam_id=c.exam_id
where 
(
(b.nick_name like '牛客%号') or
(b.nick_name regexp '[^0-9.]')=0
)
and left(lower(c.tag),1)='c'
and a.score is not null
group by a.uid,a.exam_id
order by uid asc,avg_score asc