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

select
    distinct uid,
    "activity1" as activity
from
    exam_record t1
    left join examination_info t2 on t1.exam_id = t2.exam_id
where
    year (submit_time) = 2021
    and uid not in (
        select
            uid
        from
            exam_record t1
            left join examination_info t2 on t1.exam_id = t2.exam_id
        where
            year (submit_time) = 2021
            and score < 85
    )
union all
select
    distinct uid,"activity2" as activity
from
    exam_record t1
    left join examination_info t2 on t1.exam_id = t2.exam_id
where
    score > 80
    and timestampdiff(MINUTE,start_time,submit_time) < duration/2
    and difficulty ="hard"
order by
    uid