sqlserver除了重复记录

select * from TB T where not exists (select 1 from TB where T.[学号]=[学号] And T.[科目]<[科目] )

------解决方案--------------------
select * from TB T where not exists (select 1 from TB where T.[学号]=[学号] And T.[分数]<[分数] )

------解决方案--------------------

SQL code

declare @T table([学号] varchar(2),[科目] varchar(4),[分数] int)
insert @T
select '00','语文',42 union all
select '00','数学',80 union all
select '00','英语',60 union all
select '11','英语',70

select * from @T t
where [分数]=(select max([分数]) from @T where [学号]=t.[学号])
order by 1
/*
学号   科目   分数
---- ---- -----------
00   数学   80
11   英语   70
*/

------解决方案--------------------

SQL code

select 
*
from (select *,rn=row_number()over(partition by [学号] order by [分数] desc) from table1)t
where rn=1