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

-- 学到了，字段 as ‘str’是将字段名改为‘str’ 而 ‘str’ as 字段 是将字段下的记录改为‘str’ 神奇的地方是，当字段不存在时，‘str’ as 字段 会创造一个字段！！！！ 答案代码如下：
select distinct r.uid,
case when
timestampdiff(second,r.start_time,r.submit_time)/60<=i.duration/2 and i.difficulty='hard' and r.score>80 then 'activity2'
else null end as activity
from exam_record r left join examination_info i on r.exam_id=i.exam_id where year(r.submit_time)='2021'
having activity is not null
union
select uid,'activity1' as activity
from exam_record
where year(submit_time)='2021'
group by uid
having min(score)>=85
order by uid,activity;