各科前两名查询

 

 //去掉各科第一名后的结果
(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)))

 

 

 

 

 

 

 

 

 

 

 

 

网站上的面试题！

http://zhidao.baidu.com/question/434982390.html?fr=qrl&index=1

 

有一个学生表(student),里面有 学号(student_id) 功课编号(cid) 学生成绩(corde)三个字段. 用一个SQL查询语句得出:每门功课成绩最好的前2名 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

 

 

 

 

 

http://zhidao.baidu.com/question/488692022.html?push=related

 

 

 

http://iask.sina.com.cn/b/6039491.html

 

 

oracle  分页代码学习

 

http://blog.csdn.net/liaoyuanzi

 

 

 

评论

1 楼 lf84730258 2010-07-29  

我想问一句.你是要统计各科头前两名的成绩么.那么用 row_number() over() 分析函数来实现呢？你这需求是否符合使用这种函数？