SQL2005以上版本派生表更新

SQL2005以上版本可用

USE tempdb

go

SELECT TOP 10 ID, Row= CAST (null AS INT ) INTO T FROM sysobjects

go

UPDATE ta

SET Row= row2

FROM

( SELECT *, row2=( SELECT COUNT ( 1) FROM T WHERE ID>= a. ID ) FROM T a ) AS Ta

SELECT * FROM T ORDER BY row ASC

/*

ID Row

29 1

27 2

26 3

25 4

15 5

13 6

8 7

7 8

5 9

4 10

*/

DROP TABLE t