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

with t1 as (
    select uid
    from user_info
    where nick_name REGEXP '^牛客[0-9]*号$'
    or nick_name REGEXP '^[0-9]+$'
),
t2 as (
    select exam_id
    from examination_info
    where tag REGEXP '^c'
),
t3 as (
    select uid, exam_id, score
    from exam_record
    where score IS NOT NULL
    and uid in (
        select uid 
        from t1
    )
    and exam_id in (
        select exam_id
        from t2
    )
)
select uid, exam_id, round(AVG(score), 0) as avg_score
from t3
group by uid, exam_id
order by uid, avg_score;