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

# 每次试卷得分都能到85分的人（activity1）uid和活动号
# 至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）uid和活动号

(SELECT DISTINCT uid, 'activity1' AS activity
FROM exam_record
WHERE score >= 85
AND YEAR(start_time) = '2021'
)
UNION
(SELECT DISTINCT uid, 'activity2' AS activity
FROM exam_record AS r 
LEFT JOIN examination_info AS i
ON r.exam_id = i.exam_id
WHERE i.difficulty = 'hard'
AND r.score > 80
AND TIMESTAMPDIFF(SECOND,r.start_time,r.submit_time)<=(i.duration*60*0.5)
AND YEAR(r.start_time) = '2021'
)
ORDER BY uid;