题解 | #筛选昵称规则和试卷规则的作答记录#
筛选昵称规则和试卷规则的作答记录
https://www.nowcoder.com/practice/1c5075503ccf4de1882976b2fff2c072
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
#正则表达式匹配字符串#
