在数据库中经常有Version这个选项,如果Version乱了.要更新往往要用到游标.但游标效率比较慢.下边就介绍一种比较快捷的不用游标的方法:
update version select * into #a from (select 1 as ID,1 as PID,1 as Ver union select 2, 1, 1 union select 3, 1, 1union select 4, 2, 1union select 5, 2, 1union select 6, 3, 1union select 7, 3, 1union select 8, 3, 5) aselect ID,PID,(select Count(*) from #a where PID=a.PID and ID<=a.ID) as Ver into #b from #a aupdate #a set Ver=b.Ver from #b b where #a.ID=b.ID and #a.PID=b.PIDselect * from #adrop table #adrop table #b
甚至不用临时表:
update2 select * into #a from (select 1 as ID,1 as PID,1 as Ver union select 2, 1, 1 union select 3, 1, 1union select 4, 2, 1union select 5, 2, 1union select 6, 3, 1union select 7, 3, 1union select 8, 3, 5) aupdate #a set Ver=(select Count(*) from #a where PID=b.PID and ID<=b.ID) from #a bselect * from #adrop table #a
转载于:https://www.cnblogs.com/KenBlove/archive/2009/02/10/1387254.html
