题解 | #分别满足两个活动的人#--合并查询

# 查询2021年里，所有每次试卷得分都能到85分的人的id和活动号
 # 使用if()函数是将指定值'activity1'与后面的别名区分，不然会运行报错
select uid, if(1=1, 'activity1', null) activity 
from exam_record er
where year(submit_time) = '2021'   # 仅留下2021年的答题记录
group by uid, if(1=1, 'activity1', null)
having min(score)>=85
# 查询2021年里，至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号
select distinct uid, 'activity2'
from exam_record er
join examination_info ei
on er.exam_id=ei.exam_id
where year(submit_time) = '2021'
and difficulty='hard'
and timestampdiff(minute, start_time, submit_time)<duration/2
and score>80

------------------------------完整代码如下-----------------------------------
# 合并两个结果集，并按照用户id排序
select uid, if(1=1, 'activity1', null) activity
from exam_record er
where year(submit_time) = '2021'   # 仅留下2021年的答题记录
group by uid, if(1=1, 'activity1', null)
having min(score)>=85
union
select distinct uid, 'activity2'
from exam_record er
join examination_info ei
on er.exam_id=ei.exam_id
where year(submit_time) = '2021'
and difficulty='hard'
and timestampdiff(minute, start_time, submit_time)<duration/2
and score>80
order by uid;

后来看大家的题解，发现直接用as指定别名就可以防止值与别名混淆了。不用使用if()