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

select 
er.uid,
er.exam_id,
round(avg(er.score),0) as avg_score
from exam_record er left join user_info ui on er.uid=ui.uid
left join examination_info ei on er.exam_id=ei.exam_id  #三表连接
where ui.nick_name in 
(
select 
nick_name
from user_info
where nick_name regexp '^牛客[0-9]+号$|^[0-9]+$'
)
and 
ei.tag in 
(
select
tag
from examination_info
where tag regexp '^c'
)
and er.score is not null
group by er.uid,er.exam_id
order by er.uid,avg_score