如何增加一列唯一性的ID--row_number() over()的用法 SQLServer

楼主发表于：2010-12-13 14:57:51

已知：存储过程得到下面记录：
name value
A 100
B 23
C 95
D 45

求：在记录第一列增加ID列，并且要按value排序：
结果：
ID name value
1 A 100
2 C 95
3 D 45
4 B 23

SQL code: select id = row_number() over (oder by value desc ), * from tb order by value desc select id = ( select count ( 1 ) from tb where value > t.value), * from tb t order by value desc; Select ID=Identity(int,1,1),* Into #temptable From #tb Order By Value Select * From #temptable Drop Table #temptable