列出各门课程成绩最好的两位学生--MySQL
答案:2 悬赏:80
解决时间 2021-02-11 08:29
- 提问者网友：余味
- 2021-02-10 09:36
列出各门课程成绩最好的两位学生--MySQL
最佳答案
- 二级知识专家网友：怪咖小青年
- 2021-02-10 09:53
如果考虑例如有两个同分数的第二名可以用下面的语句：
SELECt a.name, a.subject, a.score FROM stuscore AS a WHERe
(SELECt COUNT(DISTINCT score) FROM stuscore AS b
WHERe b.subject = a.subject AND b.score >= a.score) <= 2
ORDER BY a.subject ASC, a.score DESC
如果有同分数的两个第二名则删除第二名：
SELECt a.name, a.subject, a.score FROM stuscore AS a WHERe
(SELECt COUNT(*) FROM stuscore AS b
WHERe b.subject = a.subject AND b.score >= a.score) <= 2
ORDER BY a.subject ASC, a.score DESC
SELECt a.name, a.subject, a.score FROM stuscore AS a WHERe
(SELECt COUNT(DISTINCT score) FROM stuscore AS b
WHERe b.subject = a.subject AND b.score >= a.score) <= 2
ORDER BY a.subject ASC, a.score DESC
如果有同分数的两个第二名则删除第二名：
SELECt a.name, a.subject, a.score FROM stuscore AS a WHERe
(SELECt COUNT(*) FROM stuscore AS b
WHERe b.subject = a.subject AND b.score >= a.score) <= 2
ORDER BY a.subject ASC, a.score DESC
全部回答
- 1楼网友：花一样艳美的陌生人
- 2021-02-10 10:37
搜一下：列出各门课程成绩最好的两位学生--MySQL
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
大家都在看
推荐资讯