数据库结构如下:凭证号 行数 .....
10
10
10
20
20
20
20
......怎样才能使相同的凭证号的行数字段从1递增到最后一个记录凭证号 行数 .....
10 1
10 2
10 3
20 1
20 2
20 3
20 4
......
10
10
10
20
20
20
20
......怎样才能使相同的凭证号的行数字段从1递增到最后一个记录凭证号 行数 .....
10 1
10 2
10 3
20 1
20 2
20 3
20 4
......
create table #t (pzid int Identity(1,1),pzNO int, could int)
insert #t (pzNO, could) values (10, 0)
insert #t (pzNO, could) values (10, 0)
insert #t (pzNO, could) values (10, 0)
insert #t (pzNO, could) values (20, 0)
insert #t (pzNO, could) values (20, 0)
insert #t (pzNO, could) values (20, 0)gocreate table #t1 (pzid1 int Identity(1,1),pzNO1 int)
insert #t1 (pzNO1) select pzNO from #t group by pzNO
declare @min int, @max int, @pzNO int, @min1 int, @max1 int, @i int
select @min=min(pzid1) from #t1
select @max=max(pzid1) from #t1
select @max
while @min <= @max
begin
set @i = 1
select @pzNO = pzNO1 from #t1 where pzid1 = @min
select * into #t2 from #t where pzno = @pzno
select @min1 = min(pzid) from #t2
select @max1 = max(pzid) from #t2
while @min1 <= @max1
begin
update #t set could = @i where pzid = @min1
set @i = @i + 1
set @min1 = @min1 + 1
end
drop table #t2
set @min = @min + 1
end
结果
select pzno, could from #t
pzno could
----------- -----------
10 1
10 2
10 3
20 1
20 2
20 3
drop table #t
drop table #t1