表结构如下:
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
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
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', 11UPDATE a
SET dspeopno=(SELECT COUNT(1) FROM @tb b WHERE b.dscardno=a.dscardno AND b.dspeopno<=a.dspeopno)
FROM @tb aselect * from @tb001 1
001 2
001 3
002 1
002 2
003 1
004 1
update a
set dspeopno=(select count(1) from tablename where dscardno=a.dscardno and dspeopno<=a.dspeopno)
from tablename a
from tb a order by dscardno,dspeopno