表结构:
st_no--考号,char(12)
c_no --课程号,int
score--成绩,int
dates--日期,datetime
比如
st_no c_no score dates
010100010001 11 55 2001-01-01
010100010001 11 60 2002-01-01
010100010001 12 60 2002-01-01
010100010002 11 44 2001-01-01
010100010002 12 44 2001-01-01
010100010002 12 60 2002-01-01
应该选取出这个考号,这个课程号上成绩最大的成绩和日期
结果为:
010100010001 11 60 2002-01-01
010100010001 12 60 2002-01-01
010100010002 11 44 2001-01-01
010100010002 12 60 2002-01-01
请各位高手出手相助!
------解决方案--------------------
declare @t table(st_no varchar(12),c_no int,score int,dates datetime)
insert @t
select '010100010001 ', 11, 55, '2001-01-01 ' union all
select '010100010001 ', 11, 60, '2002-01-01 ' union all
select '010100010001 ', 12, 60, '2002-01-01 ' union all
select '010100010002 ', 11, 44, '2001-01-01 ' union all
select '010100010002 ', 12, 44, '2001-01-01 ' union all
select '010100010002 ', 12, 60, '2002-01-01 '
----方法1:
select * from @t as a where not exists(select 1 from @t where st_no = a.st_no and c_no = a.c_no and score > a.score)
----方法2:
select * from @t as a where score = (select max(score) from @t where st_no = a.st_no and c_no = a.c_no)
order by a.st_no,a.c_no
----方法3:
select a.* from @t as a inner join (select st_no,c_no,max(score) as score from @t group by st_no,c_no) as b
on a.st_no = b.st_no and a.c_no = b.c_no and a.score = b.score order by a.st_no,a.c_no
------解决方案--------------------
yzbaby(我是快乐的) ( ) 信誉：99 Blog 加为好友 2007-06-16 18:04:12 得分: 0
我的表我是score
这样就可以:
select * from score as a
where not exists (select 1 from score where st_no=a.st_no and c_no=a.c_no and score> a.score)
这样就不行:
select * from score
where not exists (select 1 from score where st_no=score.st_no and c_no=score.c_no and score> score.score)
order by c_no
----------
在子查詢內部使用別名也可以，但是子查詢內部和外部的名稱一樣就會有問題。
select * from score
where not exists (select 1 from score as a where a.st_no=score.st_no and a.c_no=score.c_no and a.score> score.score)
order by c_no