先按你的插入数据。然后执行下面的:declare @i int,@j int,@item varchar(10)
set @i=1
update new_table set @i=case when @item=pro_bh then @i+1 else 1 end
,new_bh=@i,@item=pro_bh
set @i=1
update new_table set @i=case when @item=pro_bh then @i+1 else 1 end
,new_bh=@i,@item=pro_bh
create table test1(
bh int,
company varchar(10),
pro_bh varchar(10))insert into test1 select 1,'aaaac','a1'
insert into test1 select 2,'bbbc ','a1'
insert into test1 select 3,'ccccc','a1'
insert into test1 select 4,'ddddd','a1'
insert into test1 select 1,'aaaac','a2'
insert into test1 select 2,'bbbc ','a2'
insert into test1 select 3,'cmmm ','a2'
insert into test1 select 4,'ddmmm','a2'
--执行处理过程
declare @i int
declare @pro_bh varchar(10)set @i=1
set @pro_bh = ''select 0 as id, a.* into #t from test1 a order by a.pro_bh,newid()update #t set @i=(case when (@pro_bh=pro_bh) then @i+1 else 1 end),id=@i,@pro_bh=pro_bh
--查看执行结果
select * from #t
--删除测试数据
drop table #t
drop table test1
这样的记录可以顺序排列