标题: oracle 查询求助 [打印本页] 作者: 啡加 时间: 2015-5-1 14:41 标题: oracle 查询求助 有三个表学生表table1： studentid studentname 科目表subject1： subjectid subjectname 分数表score1 ： studentid subjectid score
问题是 语文数学课总分前10名的学生号，姓名，总分？？该怎么写出来的，菜鸟求助大神们作者: yiyun525 时间: 2015-5-1 17:35
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。
我运行了一下是可以得到你想要的结果的。几年前学的知识都忘没了。。。惭愧啊。。作者: 赤子心 时间: 2015-5-2 22:57
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;作者: 赤子心 时间: 2015-5-2 23:00
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;
我做的有点麻烦，里面有两个子查询，还用到了伪劣。
技术不精，望指教。