各科前两名查询

最新推荐文章于 2021-10-31 07:00:00 发布

JACKASDFGHJKL

最新推荐文章于 2021-10-31 07:00:00 发布

阅读量262

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/JACKASDFGHJKL/article/details/83524166

Oracle 专栏收录该内容

95 篇文章

订阅专栏

//去掉各科第一名后的结果
(select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject));

//去掉各科前两名后的结果
(select * from ((select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject)))) minus (select * from ((select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject))) where (subject,score) in (select subject,max(score) from ((select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject))) group by subject))

//用所有的减去（去掉各科前两名后的结果）不就是想要的前两名的结果吗？

(select * from stuscore) minus ((select * from ((select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject)))) minus (select * from ((select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject))) where (subject,score) in (select subject,max(score) from ((select * from stuscore) minus (select * from stuscore where (subject,score) in (select subject,max(score) from stuscore group by subject))) group by subject)))