create table test
(
id varchar(50),
name varchar(50))
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6')
--........select * from test
/*
现在要把id列的null值替换为从001开始依次递增,直到记录的最大行,该怎样写SQL
*/
drop table test
(
id varchar(50),
name varchar(50) )
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6') select id = right('00'+cast((select count(1) from test where name < t.name) + 1 as varchar),3) , name from test tdrop table test/*
id name
------ --------------------------------------------------
001 name1
002 name2
003 name3
004 name4
005 name5
006 name6(所影响的行数为 6 行)*/
(
id varchar(50),
name varchar(50) )
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6')
declare @i int
set @i=0
update test set id=right('0000'+ltrim(@i),4),@i=@i+1
select * from test
/*id name
-------------------------------------------------- --------------------------------------------------
0001 name1
0002 name2
0003 name3
0004 name4
0005 name5
0006 name6(影響 6 個資料列)*/
drop table test
(
id varchar(50),
name varchar(50) )
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6')
go--查询
select id = right('00'+cast((select count(1) from test where name < t.name) + 1 as varchar),3) , name from test t--更改
update test
set id = right('00'+cast((select count(1) from test where name < t.name) + 1 as varchar),3)
from test t
select * from testdrop table test/*
id name
------ --------------------------------------------------
001 name1
002 name2
003 name3
004 name4
005 name5
006 name6(所影响的行数为 6 行)*/
(
id varchar(50),
name varchar(50) )
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6')
-- 生成编号
DECLARE @bh int;
SET @bh = 1000;
UPDATE test SET
@bh = @bh + 1,
id = RIGHT(@bh, 3);select * from test
/*
现在要把id列的null值替换为从001开始依次递增,直到记录的最大行,该怎样写SQL
*/
drop table test
(
id varchar(50),
name varchar(50) )
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6')
go--search
select id = right('00'+cast(row_number() over(order by name) as varchar),3) , name from test--update
update test
set id = n.id
from test m ,
(select id = right('00'+cast(row_number() over(order by name) as varchar),3) , name from test) n
where m.name = n.namedrop table test
--如果没有自增字段的话
alter table test add aaaa int identity(1,1)
go
update test set id=right(1000+aaaa,3)
go
alter table test drop column aaaa
go
select * from test
go
drop table test
/*
id name
-------------------------------------------------- --------------------------------------------------
001 name1
002 name2
003 name3
004 name4
005 name5
006 name6*/
create table test
(
id varchar ,
name varchar(50) )
insert into test(id,name)values(null,'name1')
insert into test(id,name)values(null,'name2')
insert into test(id,name)values(null,'name3')
insert into test(id,name)values(null,'name4')
insert into test(id,name)values(null,'name5')
insert into test(id,name)values(null,'name6')
go
declare @var int
select @VAR= COUNT(1)+1 from test where ID IS NULL
update a set id= CAST (@var as varchar(10)),@var=@var-1 from
(select top 10000 * from test order by name desc) a
select * from test
drop table testid name
---- --------------------------------------------------
1 name1
2 name2
3 name3
4 name4
5 name5
6 name6