表A：
KID OPRNUM OPRNUMNEXT
K0001 100 200
K0001 200 201
K0001 201 300
K0001 300 400
K0001 400 0
K0003 400 0
K0003 200 201
K0003 100 200
K0003 300 400
K0003 201 300
K0002 100 200
K0002 200 300
K0002 210 400
K0002 300 210
K0002 400 0
这里面的OPRNUMNEXT是要拿OPRNUM下一条记录的号码才是正确的
(按照OPNUM号码从小到大排，比如说K0003的OPNUM为200的下一个OPNUM号是201)。
K0001,K0003是正确的，K0002明显示错误的,就是把这一种错误的数据
找出来并且更新它们，更新效果如下：
KID OPRNUM OPRNUMNEXT
K0002 100 200
K0002 200 210
K0002 210 300
K0002 300 400
K0002 400 0
这一种SQL语句怎么样写?，请高手指点
------解决方案--------------------
- SQL code
--适用于2005-2008+--> 测试数据: @表Adeclare @表A table (KID varchar(5),OPRNUM int,OPRNUMNEXT int)insert into @表Aselect 'K0001',100,200 union allselect 'K0001',200,201 union allselect 'K0001',201,300 union allselect 'K0001',300,400 union allselect 'K0001',400,0 union allselect 'K0003',400,0 union allselect 'K0003',200,201 union allselect 'K0003',100,200 union allselect 'K0003',300,400 union allselect 'K0003',201,300 union allselect 'K0002',100,200 union allselect 'K0002',200,300 union allselect 'K0002',210,400 union allselect 'K0002',300,210 union allselect 'K0002',400,0;with maco as(select row_number() over (partition by KID order by OPRNUM) as rid, * from @表A)update @表A set OPRNUMNEXT=c.OPRNUMNEXTfrom @表A a left join (Select a.rid,a.KID,a.OPRNUM,isnull(b.OPRNUM,0) as OPRNUMNEXT from maco a left join maco b on a.KID=b.KID and a.rid=b.rid-1)con a.KID=c.KID and a.OPRNUM=c.OPRNUMselect * from @表A order by KID,OPRNUM/*KID OPRNUM OPRNUMNEXT----- ----------- -----------K0001 100 200K0001 200 201K0001 201 300K0001 300 400K0001 400 0K0002 100 200K0002 200 210K0002 210 300K0002 300 400K0002 400 0K0003 100 200K0003 200 201K0003 201 300K0003 300 400K0003 400 0*/