已知:根据Fa,Fb的唯一性修改MAXID的值,MAXID加1
maxID Fa Fb
13692 a a
13692 a a
13692 a a
13692 a b
13692 a b
13692 a c
13692 a c
13692 b c 得到:(Fa,Fb为a的MaxID由13692->13693,Fa=a,Fb=b的MaxID变成13694,依次)
maxID Fa Fb
13693 a a
13693 a a
13693 a a
13694 a b
13694 a b
13695 a c
13695 a c
13696 b c
insert into @tb
select 13692,'a','a' union all
select 13692,'a','a' union all
select 13692,'a','a' union all
select 13692,'a','b' union all
select 13692,'a','b' union all
select 13692,'a','c' union all
select 13692,'a','c' union all
select 13692,'b','c'
update a set maxid=maxid+a.rank from (
select *,dense_rank() over(order by fa,fb) as rank from @tb) a
select * from @tb
/*
13693 a a
13693 a a
13693 a a
13694 a b
13694 a b
13695 a c
13695 a c
13696 b c
*/
13692 a a
13692 a a
13692 a a
13692 a b
13692 a b
13692 a c
13692 a c
13692 b c create table tb(maxid int,fa varchar(20),fb varchar(20))
insert into tb
select 13692,'a','a'
insert into tb
select 13692,'a','a'
insert into tb
select 13692,'a','a'
insert into tb
select 13692,'a','b'
insert into tb
select 13692,'a','b'
insert into tb
select 13692,'a','c'
insert into tb
select 13692,'a','c'
insert into tb
select 13692,'b','c'
update tb set maxid=tb.maxid+c.fa1+c.fb1
from
(select *,
(select count(distinct fa) from tb as a where a.fa<=tb.fa) as fa1,
(select count(distinct fb) from tb as b where b.fb<tb.fb) as fb1
from tb) c
where tb.fa=c.fa and tb.fb=c.fb
select * from tb
maxid fa fb
----------- -------------------- --------------------
13693 a a
13693 a a
13693 a a
13694 a b
13694 a b
13695 a c
13695 a c
13696 b c(所影响的行数为 8 行)
set maxid=
maxid+(select count(distinct fa+','+fb) from tb t where (t.fa<tb.fa or t.fa=tb.fa and t.fb<=tb.fb))select * from tb
/**
maxid fa fb
----------- -------------------- --------------------
13693 a a
13693 a a
13693 a a
13694 a b
13694 a b
13695 a c
13695 a c
13696 b c(8 行受影响)
**/