create table xs(
id int IDENTITY (1,1) primary key,
姓名 char(10),
科目 char(20),
分数 int)

insert into xs values('张三','语文',90)
insert into xs values('张三','数学',77)
insert into xs values('张三','英语',99)
insert into xs values('李四','语文',83)
insert into xs values('李四','数学',87)
insert into xs values('李四','英语',85)
insert into xs values('王五','语文',94)
insert into xs values('王五','数学',88)
insert into xs values('王五','英语',93)


select a.* 
from xs a
inner join 
(select 科目,max(分数) maxp
from xs group by 科目) b
on a.科目=b.科目 and a.分数=b.maxp

id          姓名         科目                   分数
----------- ---------- -------------------- -----------
7           王五         语文                   94
3           张三         英语                   99
8           王五         数学                   88

(3 row(s) affected)