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

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