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

#1.找出昵称以"牛客"+纯数字+"号"或者纯数字组成的用户
select 
    a.uid,
    a.exam_id,
    round(avg(score),0) as avg_score
from
    exam_record as a
join (
    select
        exam_id
    from
        examination_info
    where
        tag rlike '^[cC]'
)as b
on a.exam_id = b.exam_id
where a.uid in
(
    select 
        uid 
    from
        user_info
    where
        nick_name rlike '^牛客+[0-9]+号$'
        or nick_name rlike '^[0-9]+$'
)and score is not null
group by uid,exam_id
order by uid,avg_score