重新编号的问题
数据库字段全是字符型的
数据如下
class name bianhao
a dsf
a dfdf
a sfa
b ere
b sss
b fff
b aaa
我想按class分类编号，并把编号更新到bianhao字段中，如何得到如下数据
class name bianhao
a dsf a1
a dfdf a2
a sfa a3
b ere b1
b sss b2
b fff b3
b aaa b4
[解决办法]
create table tab(class nvarchar(10) ,name nvarchar(10))
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
select class,name ,class+(select Convert(nvarchar,count(*)) from #tab b where a.class=b.class and b.id <=a.id) as bianhao
from #tab a
drop table #tab
--result
adfdfa1
adsfa2
asfaa3
baaab1
bereb2
bfffb3
bsssb4
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(class varchar(10),name varchar(10))
insert into tb(class,name) values( 'a ' , 'dsf ' )
insert into tb(class,name) values( 'a ' , 'dfdf ')
insert into tb(class,name) values( 'a ' , 'sfa ')
insert into tb(class,name) values( 'b ' , 'ere ')
insert into tb(class,name) values( 'b ' , 'sss ')
insert into tb(class,name) values( 'b ' , 'fff ')
insert into tb(class,name) values( 'b ' , 'aaa ')
go
select class , name , class + cast(px as varchar) bianhao from
(
select px=(select count(1) from tb where class=a.class and name <a.name)+1 , * from tb a
) t
order by class , bianhao
drop table tb
/*
class name bianhao
---------- ---------- ----------------------------------------
a dfdf a1
a dsf a2
a sfa a3
b aaa b1
b ere b2
b fff b3
b sss b4
（所影响的行数为 7 行）
*/
[解决办法]
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
----------------------
select * from table1
