根据这三个表,查询出每门课程的最高分的学生信息,以及所得到的分数和课程名--学生信息表
create table t_student (
stuID varchar(15) primary key ,
stuName varchar(10) not null,
age int(2) not null default 0,
sex char(2) not null,
birth datetime not null
);
--课程信息表
create table t_curriculum (
curId varchar(15) primary key,
curName varchar(10) not null,
credit int(2) not null,
learnTime int(2) not null,
teacherName varchar(10) not null
);
--成绩信息表
create table t_result(
stuId varchar(15) not null,
curId varchar(15) not null,
result float(7,2) ,
primary key(stuId,curId)
);
from t_student B,t_curriculum C,(
select stuid,curid,result
from t_result A
where not exist (select 1 from _result A.curid=curid and A.result<result)
)D
where B.stuid=D.stuid and C.curid = D.curid
select t.result,s.stuId,s.stuName,c.curName from t_result as t ,t_student as s,t_curriculum as c where c.curId = t.curId and t.stuId = s.stuId and (t.
curId ,t.result) in (select c.curId ,MAX(r.result) from t_result as r , t_curriculum as c where r.curid = c.curid group by curId) order by result;
这是我的就解法,但是我感觉能不能不用子查询呢.
[征集]分组取最大N条记录方法征集,及散分....