一個SQL查詢与更新問題，立即解決，立即給分

--适用于2005-2008+

--> 测试数据: @表A

declare @表A table (KID varchar(5),OPRNUM int,OPRNUMNEXT int)

insert into @表A

select 'K0001',100,200 union all

select 'K0001',200,201 union all

select 'K0001',201,300 union all

select 'K0001',300,400 union all

select 'K0001',400,0 union all

select 'K0003',400,0 union all

select 'K0003',200,201 union all

select 'K0003',100,200 union all

select 'K0003',300,400 union all

select 'K0003',201,300 union all

select 'K0002',100,200 union all

select 'K0002',200,300 union all

select 'K0002',210,400 union all

select 'K0002',300,210 union all

select '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.OPRNUMNEXT

from @表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)c

on a.KID=c.KID and a.OPRNUM=c.OPRNUM



select * from @表A order by KID,OPRNUM

/*

KID   OPRNUM      OPRNUMNEXT

----- ----------- -----------

K0001 100         200

K0001 200         201

K0001 201         300

K0001 300         400

K0001 400         0

K0002 100         200

K0002 200         210

K0002 210         300

K0002 300         400

K0002 400         0

K0003 100         200

K0003 200         201

K0003 201         300

K0003 300         400

K0003 400         0

*/