select id=identity(int,1,1),* # t from t order by c_a update t set c_b=right('000'+(select count(*) from #t where c_a=a.c_a and id<=a.id),3) from #t a
select id=identity(int,1,1),* into # t from t order by c_a update t set c_b=right('000'+(select count(*) from #t where c_a=a.c_a and id<=a.id),3) from #t a
select id=identity(int,1,1), * into #test from 表 begin tran update a set a.c_b=right(cast(1000+(select count(*)-1 as num from #test where c_a=a.c_a and id<=a.id) as varchar),3) from #test a truncate table 表 insert 表 select c_a,c_b from #test order by c_a,c_b if @@error=0 commit tran else rollback tran
select id=identity(int,1,1),* into # t from t order by c_aupdate t set c_b=right('000'+(select count(*) from #t where c_a=a.c_a and id<a.id),3) from #t a
create table #t(c_a varchar(10) null,c_b varchar(10) null) insert into #t(c_a) select '1003' union all select '1004' union all select '1002' union all select '1002' union all select '1001' union all select '1003' union all select '1004' union all select '1005' union all select '1002' union all select '1003' union all select '1004' union all select '1005' union all select '1003' union all select '1004' union all select '1005'Select a.*,identity(int,1,1)as row INTO #Tmp from #t a Update a set c_b=replace(str((Select Count( c_a)-1 From #Tmp Where c_a=a.c_a and row<=a.row Group By c_a),3),' ','0') From #Tmp aselect c_a,c_b from #Tmp order by c_a,c_bdrop table #t,#tmp c_a c_b ---------- ---------- 1001 000 1002 000 1002 001 1002 002 1003 000 1003 001 1003 002 1003 003 1004 000 1004 001 1004 002 1004 003 1005 000 1005 001 1005 002(所影响的行数为 15 行)
update t set c_b=right('000'+(select count(*) from #t where c_a=a.c_a and id<=a.id),3)
from #t a
update t set c_b=right('000'+(select count(*) from #t where c_a=a.c_a and id<=a.id),3)
from #t a
begin tran
update a set a.c_b=right(cast(1000+(select count(*)-1 as num from #test where c_a=a.c_a and id<=a.id) as varchar),3) from #test a truncate table 表
insert 表
select c_a,c_b from #test order by c_a,c_b
if @@error=0
commit tran
else
rollback tran
from #t a
insert into #t(c_a)
select '1003'
union all select '1004'
union all select '1002'
union all select '1002'
union all select '1001'
union all select '1003'
union all select '1004'
union all select '1005'
union all select '1002'
union all select '1003'
union all select '1004'
union all select '1005'
union all select '1003'
union all select '1004'
union all select '1005'Select a.*,identity(int,1,1)as row INTO #Tmp
from #t a
Update a set c_b=replace(str((Select Count( c_a)-1 From #Tmp Where c_a=a.c_a and row<=a.row Group By c_a),3),' ','0')
From #Tmp aselect c_a,c_b from #Tmp order by c_a,c_bdrop table #t,#tmp
c_a c_b
---------- ----------
1001 000
1002 000
1002 001
1002 002
1003 000
1003 001
1003 002
1003 003
1004 000
1004 001
1004 002
1004 003
1005 000
1005 001
1005 002(所影响的行数为 15 行)