create table test([学号] int, [科目] varchar(10), [成绩] int) insert into test select 1,'0001',584 insert into test select 2,'0001',489 insert into test select 3,'0001',587 insert into test select 1,'0002',587 insert into test select 2,'0002',586 insert into test select 3,'0002',588 select a.科目,b.学号,a.成绩 from (select max([成绩]) as '成绩',[科目] from test group by [科目]) a inner join test b on a.成绩=b.成绩 go drop table test
insert into test select 1,'0001',584
insert into test select 2,'0001',489
insert into test select 3,'0001',587
insert into test select 1,'0002',587
insert into test select 2,'0002',586
insert into test select 3,'0002',588
select a.科目,b.学号,a.成绩 from (select max([成绩]) as '成绩',[科目] from test group by [科目]) a inner join test b on a.成绩=b.成绩
go
drop table test
select 科目,max(成绩) as 成绩 from tb group by 科目