日期:2014-05-18  浏览次数:20438 次

一个简单得SQL语句
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、姓名、科目、分数

------解决方案--------------------
SQL code

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

------解决方案--------------------
SQL code
;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
*/

------解决方案--------------------
或者分组排名
SQL code
;with cte(id,姓名,科目,分数,分组排名)
as
(
select id,姓名,科目,分数,分组排名=RANK()over(partition by 科目 order by 分数 desc)
 from xs
)
select id,姓名,科目,分数 from cte
where 分组排名=1
order by id