请教一个修改数据的问题

[zzxiaoma]

表结构如下:
dscardno dspeopno
001 5
001 6
001 7
002 8
002 9
003 10
004 11
我想让他成为
dscardno dspeopno
001 1
001 2
001 3
002 1
002 2
003 1
004 1

[fcuandy]

declare @tb table(dscardno varchar(10),dspeopno int)
insert @tb
select '001' ,5
union all select
'001' , 6 union all select
'001' , 7 union all select
'002' , 8 union all select
'002' , 9 union all select
'003' , 10 union all select
'004', 11

UPDATE a
SET dspeopno=(SELECT COUNT(1) FROM @tb b WHERE b.dscardno=a.dscardno AND b.dspeopno<=a.dspeopno)
FROM @tb a

select * from @tb

001 1
001 2
001 3
002 1
002 2
003 1
004 1

[LouisXIV]

--try


update a
set dspeopno=(select count(1) from tablename where dscardno=a.dscardno and dspeopno<=a.dspeopno)
from tablename a

[wgsasd311]

select dscardno,dspeopno=(select count(1) from tb where dscardno=a.dscardno and dspeopno<=a.dspeopno)
from tb a order by dscardno,dspeopno