oj-sql

题目

SQL133 分别满足两个活动的人

思路

1. 活动1，groupby uid之后，having min最小分数；
2. 活动2，连表后，timestampdiff 筛选分数时间差；

solution

groupby min join timestampdiff

select
    uid,
    'activity1' as activity
from exam_record
group by uid
having min(score)>=85

union all

select
    distinct uid,
    'activity2' as activity
from exam_record record
join examination_info info
on (record.exam_id = info.exam_id)
where info.difficulty='hard'
  and record.score>80
  and timestampdiff(MINUTE,record.start_time,record.submit_time)*2<info.duration
order by uid