题解 | #分别满足两个活动的人#

#2021年 所有每次试卷得分都能到85分的人 activity1
#至少有一次用了一半时间就完成高难度试卷且大于80分的人 activity2
#id号和活动号 用户id排序输出

select uid,'activity1' as activity
from exam_record
where year(submit_time) = 2021
group by uid
having min(score) >= 85
union all
select distinct er.uid,'activity2' as activity
from exam_record er left join examination_info ei on er.exam_id = ei.exam_id
where year(submit_time) = 2021 
and ei.difficulty = 'hard' 
and (er.score) > 80
and timestampdiff(minute,er.start_time, er.submit_time) < ei.duration/2
group by uid
order by uid;

timestampdiff函数求时间差