已知:code值为10位数,Fa对应code值,(如Fa=a对应code=0000000479 b对应0000000668)根据Fa,Fb的值得到对应code值加1
code Fa Fb
0000000479 a a
0000000479 a b
0000000479 a c
0000000479 a d
0000000668 b a
0000000668 b b
0000000721 d a
0000000721 d b
0000000721 d c
0000001237 e a得到:(Fa为a值的code值由0000000479依次加1,变成0000000480、0000000481等)
code Fa Fb
0000000480 a a
0000000481 a b
0000000482 a c
0000000483 a d
0000000669 b a
0000000670 b b
0000000722 d a
0000000723 d b
0000000724 d c
0000001238 e a
code Fa Fb
0000000479 a a
0000000479 a b
0000000479 a c
0000000479 a d
0000000668 b a
0000000668 b b
0000000721 d a
0000000721 d b
0000000721 d c
0000001237 e a得到:(Fa为a值的code值由0000000479依次加1,变成0000000480、0000000481等)
code Fa Fb
0000000480 a a
0000000481 a b
0000000482 a c
0000000483 a d
0000000669 b a
0000000670 b b
0000000722 d a
0000000723 d b
0000000724 d c
0000001238 e a
declare @t table (code varchar(10),Fa varchar(1),Fb varchar(1))
insert into @t
select '0000000479','a','a' union all
select '0000000479','a','b' union all
select '0000000479','a','c' union all
select '0000000479','a','d' union all
select '0000000668','b','a' union all
select '0000000668','b','b' union all
select '0000000721','d','a' union all
select '0000000721','d','b' union all
select '0000000721','d','c' union all
select '0000001237','e','a'select right(replicate('0',10)+ltrim(
code+row_number() over (partition by code order by code)),10) as code
,Fa,Fb from @t/*
code Fa Fb
-------------------- ---- ----
0000000480 a a
0000000481 a b
0000000482 a c
0000000483 a d
0000000669 b a
0000000670 b b
0000000722 d a
0000000723 d b
0000000724 d c
0000001238 e a
*/
update tb set code=code+ascii(Fa)-ascii('a')+ascii(Fb)-ascii('b')+1
update tb set code=right('0000000000'+ltrim(cast(code as int)+ascii(Fa)-ascii('a')+ascii(Fb)-ascii('b')+1),10)
ltrim(cast(code as int)+
ascii(Fa)-ascii('a')+ascii(Fb)-
ascii('b')+1),10)
--上面这句就是sql2000中的语句