create table tb(ID int,[ADD] varchar(20))
insert into tb values(1001, '6500100001')
insert into tb values(1002, '6500100001')
insert into tb values(1003, '6500100003')
insert into tb values(1004, '6500100003')
insert into tb values(1005, '6500100004')
insert into tb values(1006, '6500100004')
insert into tb values(1007, '6600100001')
insert into tb values(1008, '6600100001')
insert into tb values(1009, '6600100007')
insert into tb values(1010, '6600100007')
insert into tb values(1011, '6600100008')
insert into tb values(1012, '6600100008')
goupdate tb
set [add] = left(m.[add],5) + cast(50000+n.px/2 as varchar)
from tb m ,
(select * , px = (select count(1) from tb where left([add],2) = left(t.[add],2) and id < t.id) + 1 from tb t) n
where m.id = n.id and n.px % 2 = 0select * from tbdrop table tb /*
ID ADD
----------- --------------------
1001 6500100001
1002 6500150001
1003 6500100003
1004 6500150002
1005 6500100004
1006 6500150003
1007 6600100001
1008 6600150001
1009 6600100007
1010 6600150002
1011 6600100008
1012 6600150003(所影响的行数为 12 行)
*/
insert into tb values(1001, '6500100001')
insert into tb values(1002, '6500100001')
insert into tb values(1003, '6500100003')
insert into tb values(1004, '6500100003')
insert into tb values(1005, '6500100004')
insert into tb values(1006, '6500100004')
insert into tb values(1007, '6600100001')
insert into tb values(1008, '6600100001')
insert into tb values(1009, '6600100007')
insert into tb values(1010, '6600100007')
insert into tb values(1011, '6600100008')
insert into tb values(1012, '6600100008')
goupdate tb
set [add] = left(m.[add],5) + cast(50000+n.px/2 as varchar)
from tb m ,
(select * , px = (select count(1) from tb where left([add],2) = left(t.[add],2) and id < t.id) + 1 from tb t) n
where m.id = n.id and n.px % 2 = 0select * from tbdrop table tb /*
ID ADD
----------- --------------------
1001 6500100001
1002 6500150001
1003 6500100003
1004 6500150002
1005 6500100004
1006 6500150003
1007 6600100001
1008 6600150001
1009 6600100007
1010 6600150002
1011 6600100008
1012 6600150003(所影响的行数为 12 行)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-06-30 19:54:53
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,[ADD] VARCHAR(20))
INSERT INTO @T
SELECT 1001,6500100001 UNION ALL
SELECT 1002,6500100001 UNION ALL
SELECT 1003,6500100003 UNION ALL
SELECT 1004,6500100003 UNION ALL
SELECT 1005,6500100004 UNION ALL
SELECT 1006,6500100004 UNION ALL
SELECT 1007,6600100001 UNION ALL
SELECT 1008,6600100001 UNION ALL
SELECT 1009,6600100007 UNION ALL
SELECT 1010,6600100007 UNION ALL
SELECT 1011,6600100008 UNION ALL
SELECT 1012,6600100008--SQL查询如下:DECLARE @ID INT,@flag VARCHAR(2);UPDATE A SET
@ID = CASE WHEN @flag = LEFT([ADD],2) THEN ISNULL(@ID,0) + 1 ELSE 1 END,
[ADD] = LEFT([ADD],4) + '5' + RIGHT(100000 + @ID,5),
@flag = LEFT([ADD],2)
FROM @T AS A
WHERE NOT EXISTS(SELECT * FROM @T
WHERE [ADD] = A.[ADD] AND ID > A.ID);
SELECT * FROM @T;/*
ID ADD
----------- --------------------
1001 6500100001
1002 6500500001
1003 6500100003
1004 6500500002
1005 6500100004
1006 6500500003
1007 6600100001
1008 6600500001
1009 6600100007
1010 6600500002
1011 6600100008
1012 6600500003(12 行受影响)*/