SQL24 分别满足两个活动的人

题目主要信息：

  • 分别筛选出2021年每次试卷得分都能到85分的人（activity1）和至少有一次用了一半时间就完成高难度试卷且分数大于80的人（activity2）
  • 按照用户ID排序

问题拆分：

  • 筛选2021年每次试卷得分都大于等于85的人和字符串'activity1'：
    • 按照uid进行分组划分，统计每个用户的得分情况。知识点：group by
    • 选出提交时间在2021年的试卷。知识点：select...from...where...、year()
    • 对于每组要求判断最小得分不小于85。知识点：having、min()
  • 筛选2021年至少有一次用了一半时间就完成高难度试卷且分数大于80的人和字符串'activity2'：
    • 试卷信息和考试信息分布在两个表中，须将其通过exam_id连接起来。知识点：join...on...
    • 从连接后的两个表格中满足四个条件的不重复的用户ID，因为只要求至少一次下述情况(知识点：distinct、where...and...)：
      • 提交时间是2021年。year(e_r.submit_time) = 2021
      • 试卷难度是困难。e_i.difficulty = 'hard'
      • 得分大于80。e_r.score > 80
      • 只用了试卷要求时间一半不到的时间就完成。timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
  • 将两个筛选合并。知识点：union all
  • 按照用户ID排序输出。知识点：order by uid

代码：

select uid,
       'activity1' as activity
from exam_record
where year(submit_time) = 2021
group by uid
having min(score) >= 85
union all 
select distinct uid,
       'activity2' as activity
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where year(e_r.submit_time) = 2021
and e_i.difficulty = 'hard'
and e_r.score > 80
and timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
order by uid