明确题意：

输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID升序输出

问题分解：

• 找出满足活动1的人（每次试卷得分都能到85分）：
  • 筛选开始作答时间（别漏了啊，重要条件，虽然示例数据都是21年隐藏数据可不一定）：WHERE YEAR(start_time)=2021
  • 按用户ID分组：GROUP BY uid
  • 筛选最低分>=85的分组：HAVING MIN(score)>=85
  • 输出用户ID和活动号：SELECT uid, 'activity1' as activity
• 找出满足活动2的人（至少有一次用了一半时间就完成高难度试卷且分数大于80）：
  • 内连接试卷作答表和试卷信息表：exam_record JOIN examination_info USING(exam_id)
  • 筛选开始作答时间、难度、分数：YEAR(start_time)=2021 and difficulty='hard' and score>80
  • 筛选作答用时不到一半的记录（记得用秒啊，题目的示例已经解释的很清楚了，不用秒会出错）：TimeStampDiff(SECOND, start_time, submit_time)<=duration*30
  • 筛选最低分>=85的分组：HAVING MIN(score)>=85
  • 按用户ID分组：GROUP BY uid
  • 输出用户ID和活动号：SELECT uid, 'activity2' as activity

细节问题：

• 表头重命名：as
• 按用户ID升序降序：ORDER BY uid

完整代码：

SELECT uid, 'activity1' as activity
FROM exam_record
WHERE YEAR(start_time)=2021
GROUP BY uid
HAVING MIN(score)>=85

UNION ALL

SELECT DISTINCT uid, 'activity2' as activity
FROM exam_record
JOIN examination_info USING(exam_id)
WHERE YEAR(start_time)=2021 and difficulty='hard' and score>80
    and TimeStampDiff(SECOND, start_time, submit_time)<=duration*30
ORDER BY uid;