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

select
uid,
'activity1' activity
from exam_record
where year(submit_time) ='2021'
and score >=85
and uid not in (select distinct uid 
from exam_record where submit_time is null or score < 85) 
group by uid
union all 
select distinct uid,
'activity2' activity
from exam_record a
join examination_info b
on a.exam_id =b.exam_id
where difficulty ='hard'
and year(submit_time) ='2021'
and timestampdiff(SECOND, a.start_time, a.submit_time) <=b.duration*30   ---这里要用秒，用minute结果不对
and score >80
order by uid


---另外一种就是直接判断最低分数不得小于85分，更简洁

select
uid,
'activity1' activity
from exam_record
where year(submit_time) ='2021'
group by uid
having min(score)>=85
union all 
select distinct uid,
'activity2' activity
from exam_record a
join examination_info b
on a.exam_id =b.exam_id
where difficulty ='hard'
and year(submit_time) ='2021'
and timestampdiff(SECOND, a.start_time, a.submit_time) <=b.duration*30   ---这里要用秒，用minute结果不对运行不通过
and score >80
order by uid