我有一个表,里面的数据比较杂乱,我想根据登记时间来排序,拍后根据次序创建一个序号列,并把这个序号列作为主键。如我创建了一个TABLE1表,有下面两个字段,数据内容如下,
NAME VALUE
A 13
B 12
C 11SELECT NAME, VALUE FROM TABLE1 ORDER BY VALUE
返回
NAME VALUE ID
C 11 1
B 12 2
A 13 3
我就想创建这个ID列
NAME VALUE
A 13
B 12
C 11SELECT NAME, VALUE FROM TABLE1 ORDER BY VALUE
返回
NAME VALUE ID
C 11 1
B 12 2
A 13 3
我就想创建这个ID列
from table
SELECT NAME, VALUE,ROW_NUMBER() over(order by value asc) FROM TABLE1 ORDER BY VALUE
SELECT NAME, VALUE, VALUE%10 as id FROM TABLE1 ORDER BY VALUE
select * from #tb
insert into #TABLE1([NAME],VALUE) values('B','12')
insert into #TABLE1([NAME],VALUE) values('C','11')select * from #TABLE1create table #t2([NAME] varchar(50), VALUE varchar(50),[id] [int] IDENTITY (1, 1))insert into #t2
select [NAME],VALUE from #TABLE1 order by VALUE
select * from #t2drop table #TABLE1
drop table #t2仅供参考