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

-- 先统计活动一的人员，再统计活动二，再合并
-- 活动1人员，每次试卷得分都能到85分的人，根据人分组，用最低分
-- 活动2人员，至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id，关联，过滤，去重

SELECT 
    uid, 'activity1'
FROM
    exam_record
GROUP BY uid
HAVING MIN(score) >= 85 
UNION ALL 
SELECT DISTINCT
    (uid) uid, 'activity2'
FROM
    exam_record a
        INNER JOIN
    examination_info b ON a.exam_id = b.exam_id
WHERE
    b.difficulty = 'hard'
        AND TIME_TO_SEC(a.submit_time) - TIME_TO_SEC(a.start_time) <= b.duration * 30
        AND a.score > 80
ORDER BY uid;