表Table_1 结构数据如下:
ID NAME GP
aaaa 2
aaaa 1
aaaa 4
aaaa 3
bbbb 1
bbbb 2
bbbb 3 因为原来插入的时候ID(编号)是空的... 现在要批量更新它的ID字段
条件如下:
NAME 字段如果是"aaaa"的话 ID字段就更新为A200900001,A200900002,A200900003........一直下去(根据GP字段排序再批量更新)
ID NAME GP
aaaa 2
aaaa 1
aaaa 4
aaaa 3
bbbb 1
bbbb 2
bbbb 3 因为原来插入的时候ID(编号)是空的... 现在要批量更新它的ID字段
条件如下:
NAME 字段如果是"aaaa"的话 ID字段就更新为A200900001,A200900002,A200900003........一直下去(根据GP字段排序再批量更新)
from table_1 a
join (select fid=(select count(1) from # where name=t.name and px<=t.px),name,gp from #) b
on a.name=b.name and a.gp=b.gp
insert table_1(name,gp) select 'aaaa', 2
insert table_1(name,gp) select 'aaaa', 1
insert table_1(name,gp) select 'aaaa', 4
insert table_1(name,gp) select 'aaaa', 3
insert table_1(name,gp) select 'bbbb', 1
insert table_1(name,gp) select 'bbbb', 2
insert table_1(name,gp) select 'bbbb', 3 select px=identity(int,1,1),* into # from table_1update a set a.id=b.fid
from table_1 a
join (select fid=(select count(1) from # where name=t.name and px<=t.px),name,gp from # t) b
on a.name=b.name and a.gp=b.gpselect * from table_1drop table #,table_1ID NAME GP
----------- ---------- -----------
1 aaaa 2
2 aaaa 1
3 aaaa 4
4 aaaa 3
1 bbbb 1
2 bbbb 2
3 bbbb 3(7 行受影响)
insert table_1(name,gp) select 'aaaa', 2
insert table_1(name,gp) select 'aaaa', 1
insert table_1(name,gp) select 'aaaa', 4
insert table_1(name,gp) select 'aaaa', 3
insert table_1(name,gp) select 'bbbb', 1
insert table_1(name,gp) select 'bbbb', 2
insert table_1(name,gp) select 'bbbb', 3 select px=identity(int,1,1),* into # from table_1update a set a.id=upper(left(a.name,1))+ltrim(year(getdate()))+right(10000+b.fid,4)
from table_1 a
join (select fid=(select count(1) from # where name=t.name and px<=t.px),name,gp from # t) b
on a.name=b.name and a.gp=b.gpselect * from table_1drop table #,table_1ID NAME GP
-------------------- ---------- -----------
A20090001 aaaa 2
A20090002 aaaa 1
A20090003 aaaa 4
A20090004 aaaa 3
B20090001 bbbb 1
B20090002 bbbb 2
B20090003 bbbb 3(7 行受影响)
from table_1 a
join (select fid=(select count(1) from # where name=t.name and px<=t.px),name,gp from # t) b
on a.name=b.name and a.gp=b.gp
#是个临时表,这个临时表只有一列,先根据你原始数据表的长度,把1到N(N为你表记录总数)存到这一列中。然后再拿这个单纯的整数编号表,通过判断啊,运算啊,拼接啊,得到你需要的A200900001这样的编号。再存到你正式表中。
insert tb(name,gp) select 'aaaa', 2
insert tb(name,gp) select 'aaaa', 1
insert tb(name,gp) select 'aaaa', 4
insert tb(name,gp) select 'aaaa', 3
insert tb(name,gp) select 'bbbb', 1
insert tb(name,gp) select 'bbbb', 2
insert tb(name,gp) select 'bbbb', 3
goupdate tb set id = left(name,1) + '2009' + right('0000'+cast(gp as varchar),5)select * from tb order by name , gpdrop table tb/*
ID NAME GP
-------------------- ---------- -----------
a200900001 aaaa 1
a200900002 aaaa 2
a200900003 aaaa 3
a200900004 aaaa 4
b200900001 bbbb 1
b200900002 bbbb 2
b200900003 bbbb 3(所影响的行数为 7 行)
*/
-------------------- ---------- -----------
A20090001 aaaa 1
A20090002 aaaa 2
A20090003 aaaa 3
A20090004 aaaa 4
A20090005 bbbb 1
A20090006 bbbb 2
A20090007 bbbb 3
insert tb(name,gp) select 'aaaa', 2
insert tb(name,gp) select 'aaaa', 1
insert tb(name,gp) select 'aaaa', 4
insert tb(name,gp) select 'aaaa', 3
insert tb(name,gp) select 'bbbb', 1
insert tb(name,gp) select 'bbbb', 2
insert tb(name,gp) select 'bbbb', 3
goupdate tb set id = left(name,1) + '2009' + right('0000'+cast((select count(1) from tb where name = t.name and gp < t.gp)+1 as varchar),5)
from tb tselect * from tb order by name , gpdrop table tb/*
ID NAME GP
-------------------- ---------- -----------
a200900001 aaaa 1
a200900002 aaaa 2
a200900003 aaaa 3
a200900004 aaaa 4
b200900001 bbbb 1
b200900002 bbbb 2
b200900003 bbbb 3(所影响的行数为 7 行)
*/
create table table_1(ID varchar(20), NAME varchar(10),GP int)
insert table_1(name,gp) select 'aaaa', 2
insert table_1(name,gp) select 'aaaa', 1
insert table_1(name,gp) select 'aaaa', 4
insert table_1(name,gp) select 'aaaa', 3
insert table_1(name,gp) select 'bbbb', 1
insert table_1(name,gp) select 'bbbb', 2
insert table_1(name,gp) select 'bbbb', 3
update a set a.id=upper(left(a.name,1))+ltrim(year(getdate()))+right(10000+b.fid,4)
from table_1 a
join (select fid=row_number() over(partition by [name] order by getdate()),name,gp from table_1) b
on a.name=b.name and a.gp=b.gpselect * from table_1
/*
ID NAME GP
-------------------- ---------- -----------
A20090001 aaaa 2
A20090002 aaaa 1
A20090003 aaaa 4
A20090004 aaaa 3
B20090001 bbbb 1
B20090002 bbbb 2
B20090003 bbbb 3(7 行受影响)
*/
drop table table_1