oracle中表A有字段id，name，age，表B有字段id，subject，score，id为学生学号，subject为科目，score分数

怎么查找每门功课前两名学生的ID，name ,subject ,score 。急求，写下思路和具体sql语句，非常感谢！！！

追问：没并列名次的哥们，那个score字段不在表A中啊，你查错了错了

答案：select t.*
  from (select b.id,
               a.name,
               b.score,
               b.subject,
               row_number() over(partition by b.subject order by b.score desc) rn
          from A a, B b
         where a.id = b.id) t
 where t.rn<=2

其他：这个可以拿到并列名次，
SELECT A.id, A.name, A.age, A.score
FROM (select subject, id, score, rank() over(partition by subject order by score desc) rk from B) t,
     A
WHERE t.rk <= 2 AND A.id = t.id 


如果不要并列名次，把那个rank()改为row_number()即可 select * from (select a.id,name,subject,sum(score) from a,b where a.id=b.id group by a.id,name,subject order by  sum(score)  desc ) where subject=&p_subject and rownum<=2

上一个：oracle触发器问题？
下一个：oracle分页 语句