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

【场景】：正则模糊查询

【分类】：比较运算符、regexp、rlike、like

分析思路

难点： 1.查询昵称以"牛客"+纯数字+"号"或者纯数字组成的用户 高级条件语句：rlike 与 like 不同，里面可以写正则

（1）where条件

昵称以"牛客"+纯数字+"号"或者纯数字组成的用户

• [条件]：nick_name rlike '^牛客[0-9]+号${}^{\mathrm{\prime }}ornic{k}_{n}amerlik{e}^{\mathrm{\prime }[}0-9]+\text{'}\; or\; nick\_name\; rlike\; \text{'}^[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