sql 行转列以及相同分数排名一至的方法

select distinct p.StudentId,姓名,p.总分,k.名次 as 总分排名,语文,m1.名次 as 语文排名,数学,m2.名次 as 数学排名,英语,m3.名次 as 英文排名,政治,m4.名次 as 政治排名,历史 ,m5.名次 as 历史排名,地理,物理,化学,生物,音乐,体育,美术,信息,心理 from (
select StudentId,StudentName as 姓名,SUM(总分)as 总分,SUM(语文) as 语文,SUM(数学) as 数学,SUM(英语) as 英语,SUM(政治) as 政治,SUM(历史) as 历史,SUM(地理) as 地理,SUM(物理) as 物理,SUM(化学) as 化学,SUM(生物) as 生物,SUM(音乐) as 音乐,SUM(体育) as 体育,SUM(美术) as 美术,SUM(信息) as 信息,SUM(心理) as 心理 from
(
select * from(
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,ClassId,ClassName,StudentId,StudentName,SubjectName,TotalScore,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' group by SubjectId,SubjectName,TotalScore,StudentId,StudentName,ClassId,ClassName order by ClassId,ClassName,TotalScore desc
)as A
PIVOT(MAX(TotalScore) FOR SubjectName IN (语文,数学,英语,政治,历史,地理,物理,化学,生物,音乐,体育,美术,信息,心理))E   -------行转列
) F group by F.StudentId,F.StudentName
)as p
inner join (
--校次
select * ,(select count(1) +1 from (select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' group by StudentId,StudentName) bb where bb.总分 > aa.总分) 名次
from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' group by StudentId,StudentName
)aa
) as k on p.StudentId = k.StudentId and p.总分= k.总分
left join (

----相同分数排名一至，后面的排名接着走

select * ,(select count(1)+1 from (

select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='01' group by StudentId,StudentName
) bb where bb.总分>aa.总分) 名次 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='01' group by StudentId,StudentName
)aa
) as m1 on p.StudentId = m1.StudentId and p.语文= m1.总分
left join (
select * ,(select count(1)+1 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='02' group by StudentId,StudentName
) bb where bb.总分>aa.总分) 名次 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='02' group by StudentId,StudentName
)aa
) as m2 on p.StudentId = m2.StudentId and p.数学= m2.总分
left join (
select * ,(select count(1)+1 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='03' group by StudentId,StudentName
) bb where bb.总分>aa.总分) 名次 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='03' group by StudentId,StudentName
)aa
) as m3 on p.StudentId = m3.StudentId and p.英语= m3.总分
left join (
select * ,(select count(1)+1 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='04' group by StudentId,StudentName
) bb where bb.总分>aa.总分) 名次 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='04' group by StudentId,StudentName
)aa
) as m4 on p.StudentId = m4.StudentId and p.政治=m4.总分
left join (
select * ,(select count(1)+1 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='05' group by StudentId,StudentName
) bb where bb.总分>aa.总分) 名次 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73]
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='05' group by StudentId,StudentName
)aa
) as m5 on p.StudentId = m5.StudentId and p.历史=m5.总分
order by p.总分 desc

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

select * ,(select count(1)+1 from (

select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73] 
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='01' group by StudentId,StudentName
) bb where bb.总分>aa.总分) 名次 from (
select top 50000 ROW_NUMBER() Over(order by sum(TotalScore) desc) as rowNo,StudentId,StudentName,sum(TotalScore) as 总分 from [Z_Exam_Subject_Count_32458291-f76b-4880-bf7a-19a2eb34ec73] 
where BatchId=1 and ExamId='32458291-f76b-4880-bf7a-19a2eb34ec73' and SchoolId='100000000' and SubjectId='01' group by StudentId,StudentName
)aa

效果如下：