数据库2个字段id,date 如下
1 2005-5-21
2 2005-5-20
4 2005-5-22
9 2005-5-25
10 2005-5-27
13 2005-4-1
17 2005-5-26
18 2005-5-30
需要按时间先后顺序将id从编号,求sql语句实现更新数据如下效果:
1 2005-4-1
2 2005-5-20
3 2005-5-21
4 2005-5-22
5 2005-5-25
6 2005-5-26
7 2005-5-27
8 2005-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