一、明确需求

  • 2021年，所有试卷得分都达到85分 or （至少有一次用一半时间 and 高难度试卷 and 分数>80的id和活动号，按用户ID排序输出）

二、问题分解

  1. 记录表限制日期2021年，联结信息表获得难度和时间，得表1
  2. 对记录表按uid分组筛选符合条件1得表2，新增字段activity
  3. 表1where子句进行限制获得
  4. 输出，排序

三、代码实现

with 
# 记录表限制时间，联结信息表
t1 as
(
select distinct a.*,b.difficulty,b.duration
from exam_record a 
    left join examination_info b  on a.exam_id = b.exam_id
where year(a.start_time) = '2021'
)

# 符合第一个条件得用户名单
, t2 as 
(
select uid,'activity1' as activity
from t1
group by uid
having count(score) =count(case when score>=85 then score else null end)   #这里可以用min(score)>=85
)

# 符合条件2得用户名单
, t3 as
(
select distinct uid,'activity2' as activity
from t1
where difficulty = 'hard' 
       and ((unix_timestamp(submit_time)-unix_timestamp(start_time))/60)/duration<=0.5
       and score >80 )  
       # 这里得时间相减可以使用timestampdiff(时间单位,start_time,submit_time)

select uid,activity
from t2
union all
select uid,activity
from t3
order by uid asc

四、知识拓展

  1. 最小得值满足条件则证明所有值都满足条件
  2. 时间相减函数：timestampdiff(unit,start_time,end_time)
  • unit：year,month,minute
  1. 时间转换函数：unix_timestamp(date_time)返回(date_time-某个固定时间)的秒