--> 测试数据: [test] if object_id('[test]') is not null drop table [test] go create table [test] (id varchar(4)) insert into [test] select '0001' union all select '0001' union all select '0001' union all select '0001' union all select '0001'alter table test add id1 int identity (1,1) update test set id=left(id,3)+ltrim(id1)select * from [test]id id1 ---- ----------- 0001 1 0002 2 0003 3 0004 4 0005 5(5 行受影响)游标不写了
1.select id=right('0000'+ltrim(row_number()over(order by getdate())),4) from TB
if object_id('[test]') is not null drop table [test] go create table [test] (id varchar(4)) insert into [test] select '0001' union all select '0001' union all select '0001' union all select '0001' union all select '0001'DECLARE @I INT SELECT @I=MIN(ID)-1 FROM test UPDATE test SET @I=@I+1,id=RIGHT('0000'+LTRIM(@I),4) SELECT * FROM TEST /* 0001 0002 0003 0004 0005 */
if object_id('[test]') is not null drop table [test] go create table [test] (id varchar(10)) insert into [test] select '0001' union all select '0001' union all select '0001' union all select '0001' union all select '0001'DECLARE @I INT SELECT @I=MIN(ID) FROM test UPDATE test SET id='800-'+RIGHT('0000'+LTRIM(@I-1),4),@I=@I+1SELECT * FROM TEST (所影响的行数为 5 行) (所影响的行数为 5 行)id ---------- 800-0001 800-0002 800-0003 800-0004 800-0005(所影响的行数为 5 行)
--建立测试环境 IF OBJECT_ID('test') IS NOT NULL DROP TABLE test GO CREATE TABLE test ( id int ) GO INSERT test SELECT 1 union all SELECT 1 union all SELECT 1 union all SELECT 1 union all SELECT 1 --查询declare @i int set @i=1 declare cur cursor for select id from test open cur fetch cur while @@fetch_status=0 begin update test set id=@i where current of cur set @i=@i+1 fetch cur end close cur deallocate cur select * from test --结果 /* id ----------- 1 2 3 4 5(5 行受影响) */--2 update t set id = rn from (select id,rn=row_number()over(order by id) from test ) t select * from test /* id ----------- 1 2 3 4 5*/
里面Right 关键字的作用是做什么的?
--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([id] [nvarchar](10)) INSERT INTO [tb] SELECT '0001' UNION ALL SELECT '0001' UNION ALL SELECT '0001' UNION ALL SELECT '0001' UNION ALL SELECT '0001'-->SQL查询如下: DECLARE @i INT SET @i=0 DECLARE c CURSOR FOR SELECT id FROM tb OPEN c FETCH c WHILE @@FETCH_STATUS=0 BEGIN UPDATE tb SET ID=RIGHT(100000+ID+@i,5) WHERE CURRENT OF c SET @i=@i+1 FETCH c END CLOSE c DEALLOCATE cSELECT * FROM [tb] /* id ---------- 00001 00002 00003 00004 00005(5 行受影响) */游标的方式
if object_id('[test]') is not null drop table [test]
go
create table [test] (id varchar(4))
insert into [test]
select '0001' union all
select '0001' union all
select '0001' union all
select '0001' union all
select '0001'alter table test add id1 int identity (1,1) update test set id=left(id,3)+ltrim(id1)select * from [test]id id1
---- -----------
0001 1
0002 2
0003 3
0004 4
0005 5(5 行受影响)游标不写了
go
create table [test] (id varchar(4))
insert into [test]
select '0001' union all
select '0001' union all
select '0001' union all
select '0001' union all
select '0001'DECLARE @I INT
SELECT @I=MIN(ID)-1 FROM test
UPDATE test SET @I=@I+1,id=RIGHT('0000'+LTRIM(@I),4)
SELECT * FROM TEST
/*
0001
0002
0003
0004
0005
*/
go
create table [test] (id varchar(10))
insert into [test]
select '0001' union all
select '0001' union all
select '0001' union all
select '0001' union all
select '0001'DECLARE @I INT
SELECT @I=MIN(ID) FROM test
UPDATE test SET id='800-'+RIGHT('0000'+LTRIM(@I-1),4),@I=@I+1SELECT * FROM TEST
(所影响的行数为 5 行)
(所影响的行数为 5 行)id
----------
800-0001
800-0002
800-0003
800-0004
800-0005(所影响的行数为 5 行)
IF OBJECT_ID('test') IS NOT NULL DROP TABLE test
GO
CREATE TABLE test
(
id int
)
GO
INSERT test
SELECT 1 union all
SELECT 1 union all
SELECT 1 union all
SELECT 1 union all
SELECT 1
--查询declare @i int
set @i=1
declare cur cursor for select id from test
open cur
fetch cur
while @@fetch_status=0
begin
update test set id=@i where current of cur
set @i=@i+1
fetch cur
end
close cur
deallocate cur select * from test
--结果
/*
id
-----------
1
2
3
4
5(5 行受影响)
*/--2
update t
set id = rn
from (select id,rn=row_number()over(order by id) from test )
t
select * from test
/*
id
-----------
1
2
3
4
5*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [nvarchar](10))
INSERT INTO [tb]
SELECT '0001' UNION ALL
SELECT '0001' UNION ALL
SELECT '0001' UNION ALL
SELECT '0001' UNION ALL
SELECT '0001'-->SQL查询如下:
DECLARE @i INT
SET @i=0
DECLARE c CURSOR FOR
SELECT id FROM tb
OPEN c
FETCH c
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE tb SET ID=RIGHT(100000+ID+@i,5) WHERE CURRENT OF c
SET @i=@i+1
FETCH c
END
CLOSE c
DEALLOCATE cSELECT * FROM [tb]
/*
id
----------
00001
00002
00003
00004
00005(5 行受影响)
*/游标的方式
如right('123456',3)结果就是456