select t2.level,count(t1.score) as level_cnt from exam_record as t1 left join user_info as t2 on t1.uid = t2.uid where t1.exam_id in (select exam_id from examination_info where tag="SQL") and t1.score>80 group by t2.level order by level_cnt desc 级别:简单.题目:统计作答SQL类别的试卷得分大于过80的人的用户等级分布...

select er.exam_id, count(distinct ui.uid) as uv, round(sum(score) / count(submit_time),1) as avg_score from exam_record er join user_info ui using(uid) where ui.level>5 and exam_id in #筛选出当天有作答记录的试卷id (select exam_id from exam_record ...

select t2.tag,count(*) as tag_cnt from exam_record as t1 left join examination_info as t2 on t1.exam_id = t2.exam_id where uid in (select uid from (select uid,date_format(submit_time,'%Y%m') as mth from exam_record where submit_time is not null group by uid,da...

select exam_id,duration,release_time from (select t2.exam_id, t2.duration, t2.release_time, timestampdiff(minute,start_time,submit_time) cnt, dense_rank() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) as rank_asc, dense_rank() over(partition by exam_id order by ti...

# 每个人：group by uid # 近三个有试卷作答记录的月份:排序试卷作答时间，取出前三个不同的月，统计该三个的试卷做答数和完成数。 select uid, count(score) as exam_complete_cnt from (select uid,start_time,score, dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc) as temp_rank from exam_record ) as recent_table where te...

select tag,uid,ranking from (select t2.tag,t1.uid, row_number()over(partition by t2.tag order by max(t1.score) desc, min(t1.score) desc,max(t1.uid) desc) as ranking from exam_record as t1 left join examination_info as t2 on t1.exam_id=t2.exam_id group by t2.tag,t1.uid) as t3 where ranking <4 新知...

select t1.uid, round(sum(score)/count(*)) as avg_score, round(sum(if(submit_time is not null,timestampdiff(minute,start_time,submit_time),t2.duration))/count(*),1) as avg_time_took from exam_record t1 join examination_info t2 on t1.exam_id=t2.exam_id where t2.difficulty = "hard" and t1.uid...

select exam_id, (count(*)-count(submit_time)) as incomplete_cnt, round(((count(start_time)-count(submit_time))/count(start_time)),3) as incomplete_rate from exam_record group by exam_id having incomplete_cnt!= 0 知识点:这里用到了having这个聚合后筛选,注意这个筛选是筛选出想要的,这里有一个巧妙的地方就是,不需要再聚合之前把全部完成的筛选出去,只需要再聚合之后,把完成率为1的筛选出...

select date_format(start_time,'%Y%m') as month, round(count(distinct uid,day(start_time))/count(distinct uid),2) as avg_active_days, count(distinct uid) as mau from exam_record where year(start_time)=2021 and submit_time is not null group by date_format(start_time,'%Y%m') #group by month(start_time...

select min(t1.score) as min_score_over_avg from exam_record as t1 join examination_info as t2 on t1.exam_id=t2.exam_id where t2.tag='SQL' and t1.score>= (select avg(score) FROM exam_record where exam_id in (select exam_id FROM examination_info where tag="SQL" ) ) 知识点:子查询这里用到了where后边...

create index idx_duration on examination_info(duration); create unique index uniq_idx_exam_id on examination_info(exam_id); create fulltext index full_idx_tag on examination_info(tag); 一个知识点:索引的分类:主键索引(primary index),唯一索引(unique index),常规索引(index),全文索引(fulltext index).创建索引: create [unique|fulltext]...

select count(start_time) as total_pv, count(submit_time) as complete_pv, count(distinct if(submit_time is not null,exam_id,null)) as complete_exam_cnt from exam_record; 三个知识点:count(*)和count(字段)的区别,count(*)包含所有的字段,包括null和空,而count(字段)不包括null但是包括空.count中可以加条件if (exp1,exp2,exp3) 如果exp1成立,那么结果为exp2,否则结果为...

select t1.tag, t1.difficulty, round((sum(t2.score)-max(t2.score)-min(t2.score))/(count(t2.score)-2),1) as clip_avg_score from examination_info as t1 join exam_record as t2 on t1.exam_id=t2.exam_id where t1.tag="SQL" and t1.difficulty='hard' 这里要注意一点:在使用count时:count(*)会计算空值以及null值.count(字段...

alter table user_info add school varchar(15) after level; alter table user_info change job profession varchar(10); alter table user_info modify achievement int(11) default 0; 知识点:字段名\字段类型的增加\修改增加字段在某个字段之前或者之后 alter table 表名 add 字段名 字段类型 [after|before] 某个字段 修改字段名/字段类型/二者都修改 alter table 表名 change 旧字段...