SQLSERVER分组排序函数 RANK

declare @studentnumber int;select @studentnumber=count(1) from [HistoryScoreForEachItemOfStudents] select Name '姓名', t2.banji '班级',Testnumber '考号', score+keguan+TwentySix+TwentySeven+TwentyEight+TwentyNine+Thirty '总分',CONVERT(nvarchar(20),RANK() OVER (PARTITION BY t1.banji  order by score+keguan+TwentySix+TwentySeven+TwentyEight+TwentyNine+Thirty desc))+'/'+convert(nvarchar(20),t2.RS) '班级排名', convert(nvarchar(20),RANK() over(order by  score+keguan+TwentySix+TwentySeven+TwentyEight+TwentyNine+Thirty desc))+'/'+convert(nvarchar(20),@studentnumber) '年级排名',score '卷面分',keguan '客观分', TwentySix+TwentySeven+TwentyEight+TwentyNine+Thirty'主题分'  from [HistoryScoreForEachItemOfStudents] t1 join(select banji,count(banji) RS from [HistoryScoreForEachItemOfStudents] group by banji)t2  on t1.banji=t2.banji

 

 

 

转载于:https://www.cnblogs.com/konghao/p/3696223.html