RiCo技術農場

網友問題，自己記錄一下

 

原始資料

process_sort 少3。

 

;with mycte
as
(
select t2.PROCESS_ID, PROCESS_SORT, YM, AMT1, AMT2,
ROW_NUMBER() OVER(ORDER BY cast(t1.PROCESS_SORT as int)) AS rownum
from A_PROCESS t1 left join A_MAKE t2
on t1.PROCESS_ID=t2.PROCESS_ID
where t2.YM='201212'
and (t2.AMT1>0 and t2.AMT2>0) 
)
select t1.PROCESS_ID,t1.PROCESS_SORT,t1.YM,t1.AMT1,t1.AMT2
from mycte t1 join mycte t2 
on t1.PROCESS_ID=t2.PROCESS_ID
and cast(t1.PROCESS_SORT as int)=t1.rownum

結果

 

 

讓3的AMT1>0 ，4的AMT1<0

 update A_MAKE set AMT1=1
 where PROCESS_ID='CC'  

  update A_MAKE set AMT1=-1
 where PROCESS_ID='FF'  

 

 

結果

 

 

參考

Select continuous ranges from table