题解 | #SQL16 得分不小于平均分的最低分#

得分不小于平均分的最低分

http://www.nowcoder.com/practice/3de23f1204694e74b7deef08922805b2

明确题意:

找到SQL试卷得分不小于该类试卷平均得分的用户最低得分


问题分解:

  • 找到SQL类试卷得分的平均分。
    • 试卷作答记录表关联试卷信息表:join using
    • 筛选试卷类别:where tag='SQL'
    • 取平均分:avg(score)
  • 找到分数不小于平均分的记录:where tag='SQL' and score>=(...)
  • 取最低分:min(score)

细节问题:

  • 表头重命名:as

完整代码:

select min(score) as min_score_over_avg
from exam_record
join examination_info using(exam_id)
where tag='SQL'
and score >= (
    select avg(score)
    from exam_record
    join examination_info using(exam_id)
    where tag='SQL'
)
SQL进阶 文章被收录于专栏

SQL进阶step by step

全部评论
SELECT MIN(score) min_score_over_avg FROM exam_record JOIN examination_info USING(exam_id) WHERE tag = 'SQL' and score >= AVG(score) 大佬们,可以帮忙解答一下为什么WHERE条件不能直接与聚集函数比较吗
1 回复 分享
发布于 2022-07-23 22:48
厉害
点赞 回复 分享
发布于 04-02 21:42 云南
select min(score) as min_score_over_avg from exam_record where score>=(select avg(score) from exam_record, examination_info where exam_record.exam_id=examination_info.exam_id and tag='SQL' );
点赞 回复 分享
发布于 2022-05-26 11:05
SELECT min(score) as min_score_over_avg FROM (SELECT a.score,b.tag,avg(score) AS C FROM exam_record as a LEFT JOIN examination_info as b on a.exam_id=b.exam_id WHERE tag='SQL') as d WHERE a.score> d.C 大佬能帮我看下为啥它提示没找到a.score 吗?
点赞 回复 分享
发布于 2022-03-03 17:28
过了,我以为子查询里面的a表不用写别名了,因为上面写了,结果并不是那样的,标上别名就好了
点赞 回复 分享
发布于 2022-02-05 00:07
select min(score) as min_score_over_avg from exam_record as a inner join examination_info as b on a.exam_id=b.exam_id where b.tag='SQL' AND score >= (select avg(score) from exam_record inner join examination_info as b on a.exam_id=b.exam_id where b.tag='SQL') 大佬能帮我解释一下为什么不对吗?
点赞 回复 分享
发布于 2022-02-04 23:55

相关推荐

代码飞升:别用口语,后端就写后端,前端就写前端,最后别光后悔
点赞 评论 收藏
分享
05-05 21:45
已编辑
广州大学 Java
点赞 评论 收藏
分享
评论
33
3
分享

创作者周榜

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