一个简单得SQL语句

[ASDFX110]

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)

查询各科成绩最高分的学生得ID、姓名、科目、分数

[koumingjie]

select * from xs a where not exists(select * from xs where a.分数<分数 and a.科目=科目 )


;with cte AS
(
 select *,rowNum=ROW_NUMBER()over(partition by 科目 order by 分数 desc) from xs
)
select * from cte where rowNum=1

[苦苦的潜行者]

;with cte(科目,分数)
as
(
select 科目,max(分数) from xs 
group by 科目
)
select a.* from xs a,cte b
where a.科目=b.科目 and a.分数=b.分数 
/*
(3 行受影响)
id 姓名  科目  分数
-- ----  ----   --
7	王五      	语文                	94
3	张三      	英语                	99
8	王五      	数学                	88
*/

[苦苦的潜行者]

或者分组排名

;with cte(id,姓名,科目,分数,分组排名)
as
(
select id,姓名,科目,分数,分组排名=RANK()over(partition by 科目 order by 分数 desc)
 from xs
)
select id,姓名,科目,分数 from cte
where 分组排名=1
order by id