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

1. 筛选所有得分都大于85分的人：

• 最初想法sum(case when 条件 then else end)和count比较
• 更简便组内求最小值和85比较

2. 至少有一次用了一半时间就完成高难度试卷且分数大于80的人

• 查找符合两个条件的记录，选出distinct uid

3. uinon all

SELECT temp.* FROM
(SELECT uid, "activity1" as activity
FROM exam_record
where YEAR(submit_time)=2021
group by uid
having count(uid) = sum(CASE WHEN score >= 85 THEN 1 ELSE 0 END)
union all
select temp2.uid, "activity2" as activity from (
SELECT distinct er.uid FROM exam_record er 
LEFT JOIN examination_info ei
USING (EXAM_ID)
WHERE TIMESTAMPDIFF(SECOND,er.start_time,er.submit_time) <= 30*ei.duration 
AND er.score > 80 AND YEAR(er.submit_time)=2021 AND ei.difficulty='hard') temp2) temp
ORDER BY temp.uid