题解 146 | #0级用户高难度试卷的平均用时和平均得分#

【场景】：存在空值

【分类】：空值处理、if 函数、ifnull函数

分析思路

难点：

1.如何处理未完成的默认试卷最大考试时长和0分处理？

ifnull(expr1,expr2）: 如果expr1不为空结果为expr1，expr1为空结果为expr2

if(expr1 is null, expe1,expr2）

（1）先对空值做处理

未完成的默认试卷最大考试时长和0分处理

• [使用]：ifnull(timestampdiff(minute,start_time,submit_time),duration)
• [使用]：ifnull(score,0)

（2）统计每个0级用户所有的高难度试卷考试平均用时和平均得分

• [条件]：avg(), 在计算均值时不考虑null

求解代码

方法一：

with子句

with
    main as(
        #先对空值做处理;未完成的默认试卷最大考试时长和0分处理
        select
            a.uid,
            b.exam_id,
            ifnull(timestampdiff(minute,start_time,submit_time),duration) as time,
            ifnull(score,0) as score
        from user_info a, examination_info b, exam_record c
        where a.uid = c.uid and b.exam_id = c.exam_id
        and level = 0
        and difficulty = 'hard'
    )

#统计每个0级用户所有的高难度试卷考试平均用时和平均得分
select
    uid,
    round(avg(main.score),0) as avg_score,
    round(avg(time),1) as avg_time_took
from exam_record
join main using(uid)
group by uid

方法二：

多表连接

#统计每个0级用户所有的高难度试卷考试平均用时和平均得分
select
    uid,
    round(avg(main.score),0) as avg_score,
    round(avg(time),1) as avg_time_took
from exam_record
join(
    #先对空值做处理;未完成的默认试卷最大考试时长和0分处理
    select
        a.uid,
        b.exam_id,
        ifnull(timestampdiff(minute,start_time,submit_time),duration) as time,
        ifnull(score,0) as score
    from user_info a, examination_info b, exam_record c
    where a.uid = c.uid and b.exam_id = c.exam_id
    and level = 0
    and difficulty = 'hard'
) main using(uid)
group by uid