通过代码
SELECT
exam_record.uid,
round(avg(IF(score is NULL,0,score)),0) avg_score,
ROUND((sum(TIMESTAMPDIFF(MINUTE,start_time,submit_time))
+sum(if(submit_time is null ,duration,0))) / count(*),1) avg_time_took
FROM
exam_record
RIGHT JOIN
examination_info
ON
exam_record.exam_id = examination_info.exam_id
WHERE
exam_record.uid in
(select
user_info.uid
from
user_info
left join
exam_record
on
user_info.uid = exam_record.uid
where
level = 0)
and difficulty = 'hard'
GROUP BY
exam_record.uid
思路
这道题要求没多少啊,所以我们就直接简单粗暴来
请输出每个0级用户所有的高难度试卷考试平均用时和平均得分,未完成的默认试卷最大考试时长和0分处理
1. 0级用户筛选,我们选择用where in
select
user_info.uid
from
user_info
left join
exam_record
on
user_info.uid = exam_record.uid
where
level = 0
2.考试平均用时和得分
用时保留一位小数,得分保留整数,if round, timesatamp都是老熟人啊就不多说了
round(avg(IF(score is NULL,0,score)),0) avg_score,
ROUND((sum(TIMESTAMPDIFF(MINUTE,start_time,submit_time))
+sum(if(submit_time is null ,duration,0))) / count(*),1) avg_time_took
还有最后的高难度跟group by 很简单的题啊。