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

分别满足两个活动的人

http://www.nowcoder.com/practice/a126cea91d7045e399b8ecdcadfb326f

分析初始错误答案

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
全部评论

相关推荐

05-29 20:34
门头沟学院 C++
KarlAllen:得做好直接春招的准备。学历差的话,一是面试要求会比学历好的严格不少,二是就算面试通过了也会被排序。总之暑期和秋招对于学历差的就是及其不友好
点赞 评论 收藏
分享
代码飞升:别用口语,后端就写后端,前端就写前端,最后别光后悔
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务