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

with t1 as(
    select uid, nick_name
    from user_info
    where nick_name regexp '^牛客[0-9]+号$|^[0-9]+$'
    # regexp 正则函数进行匹配：^表示匹配字符开始位置；$表示匹配字符结束位置;[...]表示匹配字符集合；+表示匹配前面的子表达式一次或多次；|表示或者。
),
t2 as (
    select exam_id, tag
    from examination_info
    where tag regexp '^[Cc]'
)
select er.uid uid, er.exam_id exam_id, round(avg(score),0) as avg_score
from exam_record er join t1 on er.uid=t1.uid
join t2 on er.exam_id=t2.exam_id  
where submit_time is not null
group by uid, exam_id
order by uid, avg_score