题解 | #0级用户高难度试卷的平均用时和平均得分#
0级用户高难度试卷的平均用时和平均得分
http://www.nowcoder.com/practice/bb474c6cbd77478fb6d9fc86934d0ebb
-
- SELECT uid , ROUND(avg(score),0) AS avg_score,
-
- ROUND(avg(yongshi),1) AS avg_time_took
-
- FROM
-
- (SELECT uid,
-
- IF(submit_time IS NULL,duration,timestampdiff(minute,start_time,submit_time)) yongshi,
-
- IF(score IS NULL,0,score) as score
-
- FROM exam_record er LEFT JOIN examination_info ei
-
- ON er.exam_id = ei.exam_id
-
- WHERE difficulty = 'hard') a
-
- WHERE uid IN (SELECT uid FROM user_info WHERE level = 0)
-
- GROUP BY uid
