一个考试成绩表ExamPaper
ID StuID Report ExamTime
1 101 50 2009-6-30
2 101 60 2009-7-30
3 102 50 2009-6-30
4 102 50 2009-7-30
5 103 80 2009-6-30
因为有补考的,所以存在一个人有多条记录的情况,现在我要取出每个人考试成绩最高的那条记录,我写的SQL语句:select * from ExamPaper a Where Report in (select max(b.Report) as MaxReport from ExamPaper b Group by b.StuID Having a.StuID=b.StuID)执行后发现一个问题,如果多次考试成绩相同,如上表中的102,还是会出现多条记录。我想每个人只要一条记录,怎么解决呢?
ID StuID Report ExamTime
1 101 50 2009-6-30
2 101 60 2009-7-30
3 102 50 2009-6-30
4 102 50 2009-7-30
5 103 80 2009-6-30
因为有补考的,所以存在一个人有多条记录的情况,现在我要取出每个人考试成绩最高的那条记录,我写的SQL语句:select * from ExamPaper a Where Report in (select max(b.Report) as MaxReport from ExamPaper b Group by b.StuID Having a.StuID=b.StuID)执行后发现一个问题,如果多次考试成绩相同,如上表中的102,还是会出现多条记录。我想每个人只要一条记录,怎么解决呢?
from Exampaper t
where not exists(select * from Exampaper where t.stuid=stuid and t.report<report)
from Exampaper t
where report=(select top 1 report from Exampaper where t.stuid=stuid order by report desc )
*
from
ExamPaper t
where
not exists(select 1 from ExamPaper where StuID=t.StuID and (Report>t.Report or Report=t.Report and ExamTime<t.ExamTime))
如果有分数相同的,取时间较早的
from ExamPaper t
where not exist(select 1 from ExamPaper where StuID=t.StuID and Report >t.Report)
from exampaper
group by stuid不至于这么简单吧?