题解 | #筛选昵称规则和试卷规则的作答记录#
筛选昵称规则和试卷规则的作答记录
http://www.nowcoder.com/practice/1c5075503ccf4de1882976b2fff2c072
利用rlike进行模糊查询,在利用正则表达式进行匹配。
SELECT uid,
exam_id,
round(AVG(score),0) as avg_score
from exam_record
where 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 RLIKE '^(C|c).*'
)
and score is not NULL
group by uid,exam_id
order by uid,avg_score;