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

# 获取活动1的用户
select t2.uid,'activity1' as activity from exam_record as t2 
where year(t2.submit_time) = 2021 
group by t2.uid having min(t2.score) >=85 
union
# 获取活动2的用户
select t1.uid,'activity2' as activity from (
select e.uid,
score from exam_record as e left join examination_info as e2 
on e.exam_id=e2.exam_id 
where e2.difficulty='hard' and year(e.submit_time)=2021 and 
TIMESTAMPDIFF(MINUTE,e.start_time,e.submit_time) < e2.duration/2 
) t1 group by t1.uid having min(t1.score) >=80 order by uid