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

select 
u.uid as uid,
round(avg(if(r.score is null,0,r.score)),0) as avg_score,
round(avg(if(r.submit_time is null,duration,TIMESTAMPDIFF(minute,start_time,submit_time))),1) as avg_time_took  
from 
user_info as u #表1
left join 
exam_record as r #表3
on u.uid=r.uid 
left join 
examination_info as e #表2
on r.exam_id=e.exam_id 
where u.level=0 and e.difficulty='hard' 
group by u.uid 
# round(avg(if(r.score is null,0,r.score)),0)

# 考核知识点（难点）：聚合函数的叠加使用

# round(avg(if(r.score is null,0,r.score)),0)

# ceil是向上取整,floor是向下取整，这两个都可以

# ceil(avg(if(r.score is null,0,r.score)))

# floor(avg(if(r.score is null,0,r.score)))

# 另一个难点是：时间函数的使用

# timestampdiff(minute,submit_time,start_time),前面中的duration只是一个返回条件，不满足时则返回这个条件值

# TIMESTAMPDIFF函数用于计算两个日期的时间差，minute是以分钟的形式

# TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

注：以上笔记内容仅为个人认为的难点，仅供自己记录，如有题友有不同的见解，欢迎，还请大家多多指教