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

1.以"牛客"+纯数字+"号"或者纯数字组成的用户 nick_name RELIKE '^牛客[0-9]+号${}^{\mathrm{\prime }}orrlik{e}^{\mathrm{\prime }[}0-9]+\text{'}\; or\; rlike\; \text{'}^[0-9]+$'

2.对于字母c开头的试卷类别（如C,C++,c#等）tag like 'c%'

3.已完成的试卷ID score is not null

4.平均得分 avg(score)

5.按用户ID、平均分升序排序 uid asc,avg(score) desc

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