求一SQL语句 在线等!请大家指点
有一表A 有字段 B(主键),C,D
D字段里面现在的值都是0,请问如何修改成类似于自增的值1,2,3,4,5......
声明:数据类型不能改成自增的
[解决办法]
create table A(B int,C int,D int)
insert into A select 6,2,0
insert into A select 7,4,0
insert into A select 8,1,0
insert into A select 9,7,0
insert into A select 5,3,0
go
declare @i int
set @i=0
update A set @i=@i+1,D=@i
select * from A
/*
B C D
----------- ----------- -----------
6 2 1
7 4 2
8 1 3
9 7 4
5 3 5
*/
go
drop table A
go
[解决办法]
create table A(B int,C int,D int)
insert into A select 6,2,0
insert into A select 7,4,0
insert into A select 8,1,0
insert into A select 9,7,0
insert into A select 5,3,0
sqlserver2005中
select a.b,a.c,ROW_NUMBER ( ) OVER ( order by b) d from a
--结果
b c d
----------- ----------- --------------------
5 3 1
6 2 2
7 4 3
8 1 4
9 7 5
(5 行受影响)
sql2000不用iden看着像是不成
[解决办法]
建一个相当于A的视图,再用楼上的方法试试!