我有一个成绩表如下：
学生id 语文 数学 考试编号
200870101 110 50 1
200870102 85 87 1
200870101 98 100 2
200870102 80 98 2现在我想把每个学生的历次考试都显示在一起出来，包括有每次考试总分、单科的排名。（注，排名是当次考试的排名，如考试编号是1，表示第一次考试）
如下：学生id 语文 语文名次 数学 数学名次 总分 总分名次 考试编号
200870101 110 1 50 2 160 2 1
200870101 98 1 100 1 198 1 2
200870102 85 2 87 1 172 2 1
200870102 80 1 98 2 178 1 2该怎么写sql??
学生id 语文 数学 考试编号
200870101 110 50 1
200870102 85 87 1
200870101 98 100 2
200870102 80 98 2现在我想把每个学生的历次考试都显示在一起出来，包括有每次考试总分、单科的排名。（注，排名是当次考试的排名，如考试编号是1，表示第一次考试）
如下：学生id 语文 语文名次 数学 数学名次 总分 总分名次 考试编号
200870101 110 1 50 2 160 2 1
200870101 98 1 100 1 198 1 2
200870102 85 2 87 1 172 2 1
200870102 80 1 98 2 178 1 2该怎么写sql??
语文名次,
数学,
数学名次=row_number() over(partition by 考试编号 order by 语文 desc),
总分=语文+数学,
总分名次row_number() over(partition by 考试编号 order by 语文+数学 desc),
考试编号
from tb
select 学生id,语文,
语文名次=row_number() over(partition by 考试编号 order by 语文 desc),
数学,
数学名次=row_number() over(partition by 考试编号 order by 数学 desc),
总分=语文+数学,
总分名次=row_number() over(partition by 考试编号 order by 语文+数学 desc),
考试编号
from tb
select 学生id,
语文,
语文名次=row_number() over(partition by 考试编号 order by 语文 desc),
数学,
数学名次=row_number() over(partition by 考试编号 order by 数学 desc),
总分=语文+数学,
总分名次=row_number() over(partition by 考试编号 order by 语文+数学 desc),
考试编号
from tb
order by 学生id
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([学生id] int,[语文] int,[数学] int,[考试编号] int)
insert [tb]
select 200870101,110,50,1 union all
select 200870102,85,87,1 union all
select 200870101,98,100,2 union all
select 200870102,80,98,2
---查询---
select 学生id,语文,
语文名次=row_number() over(partition by 考试编号 order by 语文 desc),
数学,
数学名次=row_number() over(partition by 考试编号 order by 数学 desc),
总分=语文+数学,
总分名次=row_number() over(partition by 考试编号 order by 语文+数学 desc),
考试编号
from tb
order by 学生id,考试编号---结果---
学生id 语文 语文名次 数学 数学名次 总分 总分名次 考试编号
----------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
200870101 110 1 50 2 160 2 1
200870101 98 1 100 1 198 1 2
200870102 85 2 87 1 172 1 1
200870102 80 2 98 2 178 2 2(4 行受影响)
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, chs int, math int, test int)
insert into #
select 200870101, 110, 50, 1 union all
select 200870102, 85, 87, 1 union all
select 200870101, 98, 100, 2 union all
select 200870102, 80, 98, 2select id,
chs, chs_rank=dense_rank()over(partition by test order by chs desc),
math, math_rank=dense_rank()over(partition by test order by math desc),
total=chs+math, total_rank=dense_rank()over(partition by test order by chs+math desc),
test
from # order by 1,8/*
id chs chs_rank math math_rank total total_rank test
----------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
200870101 110 1 50 2 160 2 1
200870101 98 1 100 1 198 1 2
200870102 85 2 87 1 172 1 1
200870102 80 2 98 2 178 2 2
*/
学生id 语文 数学 考试编号200870101 110 50 1
200870102 85 87 1
200870101 98 100 2
200870102 80 98 2学生id 语文 语文名次 数学 数学名次 总分 总分名次 考试编号
200870101 110 1 50 2 160 2 1
200870101 98 1 100 1 198 1 2
200870102 85 2 87 1 172 2 1
200870102 80 1 98 2 178 1 2select *,rownumber() over(partition by exam_id order by chinese asc) as ch_order from tb --语文排序结果
select *,rownumber() over(partition by exam_id order by math asc) as math_order from tb --数学排序结果
select *,rownumber() over(partition by exam_id order by itotal asc) as ch_order from (select *，chinese+math as itotal from tb) as a --总分排序select b.stu_id,b.chinese,b.ch_order,b.math,c.math_order,d.itotal,d.total_order,exam_id from
(select *,rownumber() over(partition by exam_id order by chinese asc) as ch_order from tb) as b
inner join
(select *,rownumber() over(partition by exam_id order by math asc) as math_order from tb) as c
on b.stu_id=c.stu_id and b.exam_id=c.exam_id
inner join
(select *,rownumber() over(partition by exam_id order by itotal asc) as total_order from (select *，chinese+math as itotal from tb) as a) as d
on d.stu_id=c.stu_id and d.exam_id=c.exam_id
if object_id('[stu]') is not null drop table [stu]
go
create table [stu]([stu_id] int,[chinese] int,[math] int,[exam_id] int)
insert [stu]
select 200870101,110,50,1 union all
select 200870102,85,87,1 union all
select 200870101,98,100,2 union all
select 200870102,80,98,2select stu_id,chinese,(select count(1) from stu as b where a.exam_id=b.exam_id and a.chinese<=b.chinese ) as 语文名次
,math,(select count(1) from stu as b where a.exam_id=b.exam_id and a.math<=b.math ) as 数学名次,
chinese+math as total,(select count(1) from stu as b where a.exam_id=b.exam_id and ((a.chinese+a.math)<=(b.chinese+b.math)) ) as 总分名次
from stu as a
select stu_id,chinese,(select count(1) from stu as b where a.exam_id=b.exam_id and a.chinese<=b.chinese ) as 语文名次
,math,(select count(1) from stu as b where a.exam_id=b.exam_id and a.math<=b.math ) as 数学名次,
chinese+math as total,(select count(1) from stu as b where a.exam_id=b.exam_id and ((a.chinese+a.math)<=(b.chinese+b.math)) ) as 总分名次，exam_id
from stu as a
stu_id chinese 语文名次 math 数学名次 total 总分名次 exam_id
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
200870101 110 1 50 2 160 2 1
200870102 85 2 87 1 172 1 1
200870101 98 1 100 1 198 1 2
200870102 80 2 98 2 178 2 2(4 行受影响)