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

分两个条件删选再union即可，两个重点
--如何取出分数均大于85的用户？考虑NULL的问题
--时间做差别漏了秒数

SELECT uid,'activity1' activity
FROM exam_record r JOIN examination_info i
ON r.exam_id=i.exam_id
WHERE YEAR(r.`start_time`)='2021'
GROUP BY uid
HAVING COUNT(1)=COUNT(IF(r.score>=85,1,NULL))
#使用这个筛选，而不使用MIN(score)>=85，是考虑到有用户分数存在NULL值
#如果有用户在2021年得分为 NULL,86,90 
#使用 MIN(score)>=85就会认为这个用户满足activity1，实际并不满足
#使用COUNT(1)=COUNT(IF(r.score>=85,1,NULL))就解决了这个问题
UNION ALL
SELECT uid,'activity2' activity
FROM exam_record r JOIN examination_info i
ON r.exam_id=i.exam_id
WHERE YEAR(r.`start_time`)='2021'
AND submit_time IS NOT NULL AND i.`difficulty`='hard'
AND r.`score`>80
AND TIMESTAMPDIFF(SECOND,r.start_time,r.submit_time)<=i.`duration`*60/2
ORDER BY uid,activity