题解 | SQL24 #分别满足两个活动的人#

分析初始错误答案

select uid, 'activity1' as activity
from
(select distinct uid,sum(if(score>=85,1,0)) as cnt1,count(uid) as cnt2
from exam_record 
group by uid
having cnt1=cnt2) q1
union all
select uid,'activity2' as activity
from
(select distinct uid,
sum(if(score>80 and difficulty='hard' and 
       timestampdiff(minute,start_time,submit_time)<=0.5*duration,1,0)) 
%%%对于完成时间为40min，规定时间为60min的情况，<=反而会算，<才不算
       as cnt3
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
group by uid
having cnt3>=1) q2
order by uid

通过答案

select uid, 'activity1' as activity
from
(select distinct uid,sum(if(score>=85,1,0)) as cnt1,count(uid) as cnt2
from exam_record 
group by uid
having cnt1=cnt2) q1
union all
select uid,'activity2' as activity
from
(select distinct uid,
sum(if(score>80 and difficulty='hard' and 
       timestampdiff(second,start_time,submit_time)<=30*duration,1,0)) 
       as cnt3
       %%%%%%%%%%%%%%%%%%%%%%此处更改了timestampdiff()的单位
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
group by uid
having cnt3>=1) q2
order by uid

• 易错点：

Timestampdiff()会进行四舍五入，为防止出错，最好使用更精细的单位， 例如题目要求的minute，则须用second进行筛选。

• 可改进的地方：

①每次得分都在85及以上——min(score)>=85 不必分别计算答题次数和得85分的次数，再让它们相等

②遗漏筛选条件2021年

改进答案

select distinct uid, 'activity1' as activity
from exam_record 
where year(submit_time)=2021
group by uid
having min(score)>=85
union all
select uid,'activity2' as activity
from
(select distinct uid,
sum(if(score>80 and difficulty='hard' and 
       timestampdiff(second,start_time,submit_time)<=30*duration,1,0)) 
       as cnt3
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where year(submit_time)=2021
group by uid
having cnt3>=1) q2
order by uid