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

# 特定昵称 特定试卷 要已经完成 求平均分
with
t1 as(
    select
        uid,
        ei.exam_id,
        round(avg(score),0) as avg_score
    from
        user_info as ui
        inner join
        exam_record as er using(uid)
        inner join
        examination_info as ei using(exam_id)
    where
        (nick_name rlike '^牛客[0-9]{1,}号$' or nick_name rlike '^[0-9]{1,}$')
        and
        tag rlike'^[cC]'
        and
        score is not null
    group by
        uid,
        exam_id
    order by
        uid,
        avg_score
)

select * from t1