SQL : 练习题-18题

查询各科成绩前三名的记录
大坑比。mysql不能group by 了以后取limit,所以不要想着讨巧了,我快被这一题气死了。思路有两种,第一种比较暴力,计算比自己分数大的记录有几条,如果小于3 就select,因为对前三名来说不会有3个及以上的分数比自己大了,最后再对所有select到的结果按照分数和课程编号排名即可。

select * from sc
where (
select count(*) from sc as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3
order by cid asc, sc.score desc;

第二种比较灵巧一些,用自身左交,但是有点难以理解。
先用自己交自己,条件为a.cid = b.cid and a.score<b.score,其实就是列出同一门课内所有分数比较的情况。
想要查看完整的表可以

select * from sc a 
left join sc b on a.cid = b.cid and a.score<b.score
order by a.cid,a.score;

查看,发现结果是47行的一个表,列出了类似 01号课里“30分小于50,也小于70,也小于80,也小于90”“50分小于70,小于80,小于90”…
所以理论上,对任何一门课来说,分数最高的那三个记录,在这张大表里,通过a.sid和a.cid可以联合确定这个同学的这门课的这个分数究竟比多少个其他记录高/低,
如果这个特定的a.sid和a.cid组合出现在这张表里的次数少于3个,那就意味着这个组合(学号+课号+分数)是这门课里排名前三的。
所以下面这个计算中having count 部分其实count()或者任意其他列都可以,这里制定了一个列只是因为比count()运行速度上更快。

select a.sid,a.cid,a.score from sc a 
left join sc b on a.cid = b.cid and a.score<b.score
group by a.cid, a.sid
having count(b.cid)<3
order by a.cid;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值