题解 | #SQL类别高难度试卷得分的截断平均值#

SQL类别高难度试卷得分的截断平均值

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

#方法一 select tag, difficulty, round(avg(score),1) clip_avg_score from(select exam_id, tag, difficulty from examination_info where tag='SQL' and difficulty = 'hard')t1 join (select uid,exam_id,score from exam_record )t2 on t1.exam_id = t2.exam_id where t2.score>(select min(score) from exam_record where exam_id=t1.exam_id) and t2.score <(select max(score) from exam_record where exam_id = t1.exam_id)

#方法二:窗口函数 select tag, difficulty, round(avg(score),1) clip_avg_score FROM(SELECT E1.exam_id, tag, difficulty, score, row_number() over(partition by exam_id order by score) rk1, row_number() over(partition by exam_id order by score desc) rk2 from examination_info E1 join exam_record E2 on E1.exam_id = E2.exam_id where tag = 'SQL' and difficulty = 'hard' and score is not null)t1 where t1.rk1 > 1 and t1.rk2 > 1 group by tag,difficulty

#方法三 select tag, difficulty, round((sum(score) - max(score) - min(score)) / (count(score) - 2), 1) as clip_avg_score from exam_record join examination_info using(exam_id) where tag="SQL" and difficulty="hard" and score is not null

全部评论

相关推荐

05-29 09:02
门头沟学院 Java
点赞 评论 收藏
分享
我是没经验的毕业生,这啥情况啊会不会是hr在刷kpi
JamesGosli...:字节boss属于是群发了,我都快入职字节了,其他部门还在和我boss打招呼
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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