数据库字段全是字符型的数据如下
class name bianhao
a dsf
a dfdf
a sfa
b ere
b sss
a fff
a aaa我想按class分类编号,并把编号更新到bianhao字段中,如何得到如下数据class name bianhao
a dsf a0001
a dfdf a0002
a sfa a0003
b ere b0001
b sss b0002
a fff a0004
a aaa a0005
class name bianhao
a dsf
a dfdf
a sfa
b ere
b sss
a fff
a aaa我想按class分类编号,并把编号更新到bianhao字段中,如何得到如下数据class name bianhao
a dsf a0001
a dfdf a0002
a sfa a0003
b ere b0001
b sss b0002
a fff a0004
a aaa a0005
declare @a varchar(10),@i int
select @a='',@i=0
update table1 set bianhao=class+right('0000'+rtrim(@i),4),@i=(case when @a=class then @i+1 else 1 end),@a=class 好像不行,是不是要 group一下,不知道怎么写
insert test select 'a','dsf',null
union all select 'a','dfdf',null
union all select 'a','sfa',null
union all select 'b','ere',null
union all select 'b','sss',null
union all select 'a','fff',null
union all select 'a','aaa',nullselect id=identity(int,1,1),* into #t from testupdate test set bianhao=b.bianhao from
(
select class,name,
bianhao=class+'000'+cast((select count(1)+1 from #t where class=a.class and id<a.id) as varchar)
from #t a
)b
where test.class=b.class and test.name=b.nameselect * from testdrop table test,#tclass name bianhao
---------- -------------------- --------------------
a dsf a0001
a dfdf a0002
a sfa a0003
b ere b0001
b sss b0002
a fff a0004
a aaa a0005(所影响的行数为 7 行)
insert into tab select 'a', 'dsf' ,''
union select 'a', 'dfdf',''
union select 'a', 'sfa',''
union select 'b', 'ere',''
union select 'b', 'sss',''
union select 'b', 'fff',''
union select 'b', 'aaa',''
-- 没主键嘛?name也没规律可循, 只能 用 identity 加个标识了
select * ,identity(int,1,1) as id
into #tab
from tab
update tab set bianhao=c.bianhao
from tab,(
select class,name ,class+(select '000'+Convert(nvarchar,count(*)) from #tab b where a.class=b.class and b.id<=a.id) as bianhao
from #tab a) c
where tab.class=c.class and tab.name=c.namedrop table #tab
a dsf a0002
a sfa a0003
b aaa b0001
b ere b0002
b fff b0003
b sss b0004
create table table_Pqs(class varchar(10), name varchar(10), bianhao varchar(10))
insert into table_Pqs(class , name)select 'a','dsf' union all
select 'a','dfdf' union all
select 'a','aaaa' union all
select 'a','ffff' union all
select 'a','yyyy' union allselect 'b','dsf' union all
select 'b','dfdf' union all
select 'c','aaaa' union all
select 'd','ffff' union all
select 'd','yyyy' union all
select 'e','dsf'--增加标识
ALTER TABLE dbo.table_Pqs ADD id int NOT NULL IDENTITY (1, 1)--执行更改
update table_Pqs set bianhao=new_bianhao from
(
Select id as sid, class + right('0000'+(Select cast(Count(1) as varchar) From table_Pqs Where class = A.class And id <= A.id),4) As new_bianhao
From table_Pqs as a) Pqs where table_Pqs.id=Pqs.sid--删除增加的字段
ALTER TABLE dbo.table_Pqs DROP COLUMN id
insert @test select 'a','dsf',null
union all select 'a','dfdf',null
union all select 'a','sfa',null
union all select 'b','ere',null
union all select 'b','sss',null
union all select 'a','fff',null
union all select 'a','aaa',null
select id=identity(int,1,1),* into # from @testupdate #
set bianhao = class+right('0000'+rtrim((select count(1)+1 from # where class = a.class and id < a.id)),4)
from # a
select class,name,bianhao from #
drop table #
/*class name bianhao
---------- -------------------- --------------------
a dsf a0001
a dfdf a0002
a sfa a0003
b ere b0001
b sss b0002
a fff a0004
a aaa a0005*/