题解|聚合 GROUP 时间差分钟|#分别满足两个活动的人#

### activity1: date(,%Y) = '2021',2021年每次试卷得分都在85分以上
### activity2：2021年，至少有一次用了一半时间（submit_time-start_time）<0.5*duration,且分数在80分以上

SELECT uid, 'activity1' AS activity
FROM exam_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid
HAVING min(score) >= 85
UNION ALL
SELECT exam_record.uid, 'activity2' AS activity
FROM exam_record
LEFT JOIN examination_info ON exam_record.exam_id = examination_info.exam_id
WHERE YEAR(exam_record.submit_time) = 2021
AND examination_info.difficulty = 'hard'
AND exam_record.score > 80 
AND timestampdiff(minute,exam_record.start_time,exam_record.submit_time) < 0.5*examination_info.duration
ORDER BY uid