oracle 查询求助

啡加

有三个表学生表table1：  studentid  studentname   科目表subject1：   subjectid   subjectname  分数表score1 ：  studentid  subjectid  score   
问题是 语文数学课总分前10名的学生号，姓名，总分？？该怎么写出来的，菜鸟求助大神们

yiyun525

select student.studentname, aa.* from student,(select studentid as 'id', sum(score) as 'SUM' from score where score.subjectid in (1,2) group by studentid ) as aa where aa.id = student.studentid order by SUM desc limit 10;
上面语句中的1，2指的是语文数学的id。我自己建表的时候写的语文数学ID是1，2.这里改成你自己的id。
我运行了一下是可以得到你想要的结果的。几年前学的知识都忘没了。。。惭愧啊。。

赤子心

select * from (select a.*,rownum rm from
(select min(st.studentid) "学号",st.studentname "姓名",sum(sc.score) "总分"
from student st,subject1 su, score1 sc
where st.studentid=sc.studentid and su.subjectid=sc.subjectid  
and su.subjectname in('china','math') group by st.studentname order by sum(sc.score) desc
) a) where rm<=10;

赤子心

select * from (select a.*,rownum rm from
(select min(st.studentid) "学号",st.studentname "姓名",sum(sc.score) "总分"
from student st,subject1 su, score1 sc
where st.studentid=sc.studentid and su.subjectid=sc.subjectid  
and su.subjectname in('china','math') group by st.studentname order by sum(sc.score) desc
) a) where rm<=10;
我做的有点麻烦，里面有两个子查询，还用到了伪劣。
技术不精，望指教。