1、create table t(id1 int,id2 int,ct varchar(8))
goinsert into t select 1,isnull(max(id2),0)+1,'t1' from t where id1=1
insert into t select 1,isnull(max(id2),0)+1,'t2' from t where id1=1
insert into t select 1,isnull(max(id2),0)+1,'a1' from t where id1=1
insert into t select 1,isnull(max(id2),0)+1,'a2' from t where id1=1
insert into t select 2,isnull(max(id2),0)+1,'a5' from t where id1=2
insert into t select 2,isnull(max(id2),0)+1,'a6' from t where id1=2
insert into t select 2,isnull(max(id2),0)+1,'a7' from t where id1=2
insert into t select 2,isnull(max(id2),0)+1,'a8' from t where id1=2
insert into t select 3,isnull(max(id2),0)+1,'dd' from t where id1=3
insert into t select 3,isnull(max(id2),0)+1,'ss' from t where id1=3
goselect * from t
godrop table t
go
goinsert into t select 1,isnull(max(id2),0)+1,'t1' from t where id1=1
insert into t select 1,isnull(max(id2),0)+1,'t2' from t where id1=1
insert into t select 1,isnull(max(id2),0)+1,'a1' from t where id1=1
insert into t select 1,isnull(max(id2),0)+1,'a2' from t where id1=1
insert into t select 2,isnull(max(id2),0)+1,'a5' from t where id1=2
insert into t select 2,isnull(max(id2),0)+1,'a6' from t where id1=2
insert into t select 2,isnull(max(id2),0)+1,'a7' from t where id1=2
insert into t select 2,isnull(max(id2),0)+1,'a8' from t where id1=2
insert into t select 3,isnull(max(id2),0)+1,'dd' from t where id1=3
insert into t select 3,isnull(max(id2),0)+1,'ss' from t where id1=3
goselect * from t
godrop table t
go
goinsert into t select 2,1,'a5'
insert into t select 2,2,'a6'
insert into t select 2,4,'a8'
goupdate a set id2=(select count(*) from t where id1=a.id1 and id2<=a.id2) from t a
goselect * from t
go