一个简单得SQL语句解决办法

一个简单得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 bwhere a.科目=b.科目 and a.分数=b.分数 /*(3 行受影响)id 姓名  科目  分数-- ----  ----   --7    王五          语文                    943    张三          英语                    998    王五          数学                    88*/
[解决办法]
或者分组排名
SQL code
;with cte(id,姓名,科目,分数,分组排名)as(select id,姓名,科目,分数,分组排名=RANK()over(partition by 科目 order by 分数 desc) from xs)select id,姓名,科目,分数 from ctewhere 分组排名=1order by id