题解 | #分别满足两个活动的人#
分别满足两个活动的人
https://www.nowcoder.com/practice/a126cea91d7045e399b8ecdcadfb326f
select
uid,'activity1'
from exam_record a
left join examination_info b on a.exam_id=b.exam_id
where
uid not in (select uid from exam_record where score<85)
and year(submit_time)='2021'
union
select uid,'activity2'
from
(
select
uid
,score
,duration
,timestampdiff(second,start_time,submit_time)/60 diff -- 时间如果用分钟,比如40分钟59秒,返回40分,因此用秒更精确
from exam_record a
left join examination_info b on a.exam_id=b.exam_id
where year(submit_time)='2021'
and difficulty='hard'
and duration/2>=timestampdiff(second,start_time,submit_time)/60
) t
where diff >=0 and score>80
