题解 | #0级用户高难度试卷的平均用时和平均得分#
0级用户高难度试卷的平均用时和平均得分
http://www.nowcoder.com/practice/bb474c6cbd77478fb6d9fc86934d0ebb
解题关键在于对空值的处理上。
对空值的处理有两种方法
#方法1：coalesce(A,B),如果A为空则返回B,不为空则返回A
#方法2：IF(A IS NULL ,B,C)如果A为空则返回B，不为空则返回C完整代码
SELECT uid, ROUND(AVG(score_new),0) avg_score, ROUND(AVG(cost_time),1) avg_time_took FROM (SELECT a.uid,start_time, COALESCE(score,0) score_new, #方法1：coalesce(A,B),如果A为空则返回B,不为空则返回A IF (submit_time IS NULL,b.duration,TIMESTAMPDIFF(minute,start_time,submit_time)) cost_time #IF(A IS NULL ,B,C)如果A为空则返回B，不为空则返回C FROM exam_record a LEFT JOIN examination_info b ON a.exam_id=b.exam_id LEFT JOIN user_info c ON a.uid=c.uid WHERE difficulty='hard' AND level='0')t1 GROUP BY uid
SQL解题集 文章被收录于专栏
这是牛客SQL相关的解题集
