rank() over(partition by [Class] order by [Score] desc)

with tbl([Name],[Class],[Score]) as(

select N'张三',N'一班',648

union all

select N'李四',N'一班',215

union all

select N'王五',N'一班',321

union all

select N'聊六',N'一班',523

union all

select N'张三',N'二班',256

union all

select N'李四',N'二班',512

union all

select N'王五',N'二班',128

union all

select N'聊六',N'二班',1024

)

 

--select * from (

--select [Name],[Class],rank() over(partition by [Class] order by [Score] desc) r from tbl)a

--order by a.r

 

select * from (

select [Name],[Class],dense_rank() over(partition by [Class] order by [Score] desc) r from tbl)b where b.[Class]=N'一班'

order by b.r