求一SQL语句！

Question

有学生，课程，成绩三张表，学生与课程为多对多关系，成绩表中记录了学生与课程的id以及成绩，现在要求查询用分组列出课程名和得到最高分的学生名还有每门课程的最高分~
drop sequence sequence_student_id;
create sequence sequence_student_id
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
drop table student;
create table student
(
  id integer,
  name varchar(20)
);drop sequence sequence_curricula_id;
create sequence sequence_curricula_id
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
drop table curricula;
create table curricula
(
  id integer,
  name varchar(20)
);drop sequence sequence__id;
create sequence sequence__id
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
drop table ;
create table 
(
  id integer,
  student_id integer,
  curricula_id integer,
  value integer
);truncate table student;
insert into student values(sequence_student_id.nextval,'Jack');
insert into student values(sequence_student_id.nextval,'Sun');
insert into student values(sequence_student_id.nextval,'Bill');
insert into student values(sequence_student_id.nextval,'Tom');
insert into student values(sequence_student_id.nextval,'Herry');truncate table curricula;
insert into curricula values(sequence_curricula_id.nextval,'Java');
insert into curricula values(sequence_curricula_id.nextval,'C++');
insert into curricula values(sequence_curricula_id.nextval,'Math');
insert into curricula values(sequence_curricula_id.nextval,'Eng');
insert into curricula values(sequence_curricula_id.nextval,'History');
insert into curricula values(sequence_curricula_id.nextval,'COM');truncate table ;
insert into  values(sequence__id.nextval,1,1,50);
insert into  values(sequence__id.nextval,1,2,30);
insert into  values(sequence__id.nextval,2,4,50);
insert into  values(sequence__id.nextval,2,3,60);
insert into  values(sequence__id.nextval,2,1,70);
insert into  values(sequence__id.nextval,3,1,44);
insert into  values(sequence__id.nextval,3,5,80);
insert into  values(sequence__id.nextval,4,2,50);
insert into  values(sequence__id.nextval,4,5,60);
insert into  values(sequence__id.nextval,4,3,10);
insert into  values(sequence__id.nextval,4,1,90);
insert into  values(sequence__id.nextval,5,6,10);
insert into  values(sequence__id.nextval,5,4,20);
insert into  values(sequence__id.nextval,5,1,5);select * from student;
select * from curricula;
select * from ;

Answer 1

select name cur_name,value ,stu_name from(
select a.name,a.id,
rank()over(partition by a.id order by b.value desc)rk,
b.value,
c.name stu_name
from curricula a inner join  b
on a.id=b.curricula_id
inner join student c
on b.student_id=c.id)
where rk=1
order by idCUR_NAME MARK STU_NAME
Java 90 Tom
C++ 50 Tom
Math 60 Sun
Eng 50 Sun
History 80 Bill
COM 10 Herry

Answer 2

sequence_curricula_id 这个序列名太长了

Answer 3

再顶
有没有不用rank() over()，不用proc的的方法

Answer 4

SELECT c.name, s.name ,m.value
 FROM  m
 JOIN curricula c ON (c.id=m.curricula_id)
 JOIN student s   ON (m.student_id=s.id)
WHERE (curricula_id, value)
  IN (
       SELECT curricula_id,max(value) maxval
         FROM 
        GROUP BY curricula_id)

Answer 5

select t.name cur_name,c.value ,d.stu_name from(
select a.name,
max(b.value)max_value,
from curricula a inner join  b
on a.id=b.curricula_id
group by a.name
)t inner join  c on
t.max_value=c.value
inner join student d
on c.student_id=d.id

Answer 6

select name cur_name,value ,stu_name from(
select a.name,a.id,
b.value,
c.name stu_name
from curricula a inner join  b
on a.id=b.curricula_id
inner join student c
on b.student_id=c.id)t
where not exists(
select 1 from  where value>t.value and curricula_id=t.id)
order by id

Answer 7

分析函数在mysql等其它数据库上不一定能用吧
谢了哈~