主账号处理

drop table A
create table A(id int identity(1,1) not null primary key,acc varchar(100),flag int default 0,iszhu char(1) default 0)
insert A(acc)
select '101 000550 8091 111'
union all
select '101 000550 8092 121'
union all
select '101 000553 8093 111'
union all
select '101 000554 8091 111'
union all
select '101 000444 8023 101'
union all
select '101 000444 8043 101'
union all
select '101 000443 8071 101'
union all
select '101 000550 8091 111'
union all
select '101 000550 8093 111'

 

with cte1 as(
select distinct substring(acc,5,7) as number from a
),cte2 as(
select row_number() over(order by number) as id,number   from cte1
)
update A set flag=cte2.id from A,cte2 where substring(acc,5,7)=number

declare @flag int
set @flag=1
while @flag<=(select max(flag) from A)
begin
with cte3 as(
select id,min(acc) as zhanghao from a where flag=@flag and substring(acc,12,4) in('8091','8092','8093','8094','8095','8096','8097','8098','8099') group by id
)
update A set iszhu=1 where id in(select top 1 id from cte3)
set @flag=@flag+1
end

select * from A