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

select
    uid,
    'activity1' as activity
from
    exam_record as t1
    inner join examination_info as t2 on t1.exam_id = t2.exam_id
where
    year (t1.start_time) = 2021
group by
    uid
having
    min(t1.score) >= 85
union
select
    uid,
    'activity2' as activity
from
    exam_record as t1
    inner join examination_info as t2 on t1.exam_id = t2.exam_id
where
    year (t1.start_time) = 2021
    and t2.difficulty = 'hard'
    and t1.score > 80
    and timestampdiff (second, t1.start_time, t1.submit_time) <= (t2.duration * 60) / 2
group by
    uid
order by uid;