题解 | #筛选昵称规则和试卷规则的作答记录#
筛选昵称规则和试卷规则的作答记录
http://www.nowcoder.com/practice/1c5075503ccf4de1882976b2fff2c072
主要是找数字这个难搞，要用正则: 字段名 regexp'[^0-9]' 结果为0，代表都是数字
select t1.uid,t2.exam_id,round(avg(score),0) as avg_score from user_info t1 join exam_record t2 on t1.uid = t2.uid join examination_info t3 on t2.exam_id = t3.exam_id where nick_name regexp'[^0-9]'=0 or (nick_name like '牛客%号' and substring_index(substring_index(nick_name,'牛客',-1),'号',1) regexp'[^0-9]' = 0) and lower(substr(tag,1,1)) = 'c' and submit_time is not null group by t1.uid,t2.exam_id order by uid,avg_score
