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

SQL里学习通配符时LIKE和RLIKE的区别:https://blog.csdn.net/yuanjiu4221/article/details/82661424

mysql 正则表达式： https://www.runoob.com/mysql/mysql-regexp.html

select uid, exam_id, round(avg(score), 0) as avg_score
from exam_record
group by uid, exam_id
having uid in (select uid from user_info
               # 正则表达式
              where nick_name rlike '^牛客[0-9]+号$'
              or nick_name rlike '^[0-9]+$'
             )
and exam_id in (select exam_id
                from examination_info
                # 通配符
                where tag like 'C%'
                or tag like 'c%'
               )
and avg_score IS NOT NULL
order by uid, avg_score;