题解|聚合 GROUP 时间差分钟|#分别满足两个活动的人#

分别满足两个活动的人

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

### activity1: date(,%Y) = '2021',2021年每次试卷得分都在85分以上
### activity2:2021年,至少有一次用了一半时间(submit_time-start_time)<0.5*duration,且分数在80分以上

SELECT uid, 'activity1' AS activity
FROM exam_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid
HAVING min(score) >= 85
UNION ALL
SELECT exam_record.uid, 'activity2' AS activity
FROM exam_record
LEFT JOIN examination_info ON exam_record.exam_id = examination_info.exam_id
WHERE YEAR(exam_record.submit_time) = 2021
AND examination_info.difficulty = 'hard'
AND exam_record.score > 80 
AND timestampdiff(minute,exam_record.start_time,exam_record.submit_time) < 0.5*examination_info.duration
ORDER BY uid

全部评论

相关推荐

点赞 1 评论
分享
牛客网
牛客企业服务