字段按日期重新编号

字段按日期从新编号
数据库2个字段id,date 如下
12005-5-21
 22005-5-20
 42005-5-22
 92005-5-25
 102005-5-27
 132005-4-1
 172005-5-26
 182005-5-30 

需要按时间先后顺序将id从编号,求sql语句实现更新数据如下效果:
12005-4-1
22005-5-20
32005-5-21
42005-5-22
52005-5-25
62005-5-26
72005-5-27
82005-5-30


[解决办法]

SQL code

select row_number() over(order by [date] asc) id,[date] from [你的表]
[解决办法]
SQL code
Declare @A Table (ID Int, dDate Date)Insert Into @ASelect 1, '2005-5-21'Union All Select 2,'2005-5-20'Union All Select 4,'2005-5-22'Union All Select 9,'2005-5-25'Union All Select 10,'2005-5-27'Union All Select 13,'2005-4-1'Union All Select 17,'2005-5-26'Union All Select 18,'2005-5-30' Select ROW_NUMBER() OVER (Order By dDate Asc) ID, dDate From @A/*ID                   dDate-------------------- ----------1                    2005-04-012                    2005-05-203                    2005-05-214                    2005-05-225                    2005-05-256                    2005-05-267                    2005-05-278                    2005-05-30*/Update A Set A.ID=B.IDFrom @A A,(Select ROW_NUMBER() OVER (Order By dDate Asc) ID, dDate From @A) B Where A.dDate=B.dDateSelect * From @A/*ID          dDate----------- ----------3           2005-05-212           2005-05-204           2005-05-225           2005-05-257           2005-05-271           2005-04-016           2005-05-268           2005-05-30*/
[解决办法]
SQL code
select     row_number() over (order by date) as id,    date from tablename