题解 148 | #筛选昵称规则和试卷规则的作答记录#
【场景】：正则模糊查询
【分类】：比较运算符、regexp、rlike、like
分析思路
难点： 1.查询昵称以"牛客"+纯数字+"号"或者纯数字组成的用户 高级条件语句：rlike 与 like 不同，里面可以写正则
（1）where条件
昵称以"牛客"+纯数字+"号"或者纯数字组成的用户
- [条件]：nick_name rlike '^牛客[0-9]+号'
字母c开头的试卷类别（如C,C++,c#等）已完成的试卷
-
[条件]：(tag like 'C%' or tag like 'c%') and submit_time is not null 或者
-
[条件]：tag rlike "^(c|C).*" and submit_time is not null
（2）平均得分
- [条件]： group by uid,exam_id
（3）按用户ID、平均分升序排序
- [使用]：order by uid,avg_score
求解代码
方法一：
使用 like
select
a.uid,
b.exam_id,
round(avg(score)) as avg_score
from user_info a, examination_info b, exam_record c
where a.uid = c.uid
and b.exam_id = c.exam_id
and (nick_name rlike '^牛客[0-9]+号$' or nick_name rlike '^[0-9]+$')
and (tag like 'C%' or tag like 'c%')
and submit_time is not null
group by uid,exam_id
order by uid,avg_score
方法二：
使用 rlike
select
a.uid,
b.exam_id,
round(avg(score)) as avg_score
from user_info a, examination_info b, exam_record c
where a.uid = c.uid
and b.exam_id = c.exam_id
and (nick_name rlike '^牛客[0-9]+号$' or nick_name rlike '^[0-9]+$')
and tag rlike "^(c|C).*"
and submit_time is not null
group by uid,exam_id
order by uid,avg_score
方法三：
使用 regexp
select
a.uid,
b.exam_id,
round(avg(score)) as avg_score
from user_info a, examination_info b, exam_record c
where a.uid = c.uid
and b.exam_id = c.exam_id
and (nick_name regexp '^牛客[0-9]+号$' or nick_name regexp '^[0-9]+$')
and tag regexp "^(c|C).*"
and submit_time is not null
group by uid,exam_id
order by uid,avg_score
