sql语句求排名

create table #tmp( xm varchar(10),score int,ordernums int)
insert into #tmp(xm,score) values('张三', 60)
insert into #tmp(xm,score) values('李四' , 80)
insert into #tmp(xm,score) values('王五' , 75)
insert into #tmp(xm,score) values('刘二' ,100)

update a set a.ordernums =
(select count(*) from #tmp where score>=a.score)
from #tmp a

select * from #tmp