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

select
    uid,
    'activity1' activity
from
    test.examination_info ei
    join test.exam_record er on ei.exam_id = er.exam_id
where
    year (start_time) = 2021
group by
    uid
having
    min(score) >= 85
union
select
    uid,
    'activity2' activity
from
    (
        select
            uid,
            if (
                difficulty = 'hard'
                and timestampdiff (second, start_time, submit_time) / 60 <= duration / 2
                and score > 80,
                1,
                0
            ) 判断
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
        where
            year (start_time) = 2021
    ) a
group by
    uid
having
    sum(判断) >= 1
order by
    uid asc,
    activity asc