declare @OrderTable Table(Rid int identity(1,1),Orderid int)
declare @V_Count int=0
insert into @OrderTable(Orderid)
select id
from T_OMSOnlineOrder t
where t.OrderStatus in (1,2,3,4)----查询需要遍历的数据
select @V_Count=COUNT(1)
from @OrderTable
while(@V_Count>0)
begin
declare @orderid int =0
select @orderid=Orderid
from @OrderTable
where Rid=@V_Count--遍历数据
declare @OmsOnlineOrderId INT,
@AllNum INT ,
@AllSku VARCHAR(max) ,
@AllStoreSite VARCHAR(max) ,
@IsGift BIT ,
@StandardWeight DECIMAL(14,4)
set @OmsOnlineOrderId=@orderid
--重新计算商品数量 、 商品所有Sku 、 是否是赠品 、 重量
SELECT @AllNum = ISNULL(SUM(Num-ISNULL(RefundQty,0)) ,0)
,@AllSku = stuff((select ','+Sku+'('+CONVERT(VARCHAR,ISNULL(sp.Num,0)-ISNULL(sp.RefundQty,0))+')' from dbo.T_OMSOnlineOrderProduct sp WITH(NOLOCK) WHERE sp.OMSOnlineOrderId =@OmsOnlineOrderId AND sp.RefundStatus<>6 for xml path('')), 1, 1, '') ,
@allStoreSite = stuff((select ','+storesite from dbo.T_OMSOnlineOrderProduct sp WITH(NOLOCK) where sp.OMSOnlineOrderId = @orderid AND ISNULL(sp.StoreSite,'')<>'' AND sp.RefundStatus<>6 for xml path('')), 1, 1, '')
,@IsGift = CASE WHEN SUM(CONVERT(INT,ISNULL(IsGift,0)))=0 THEN 0 ELSE 1 END
,@StandardWeight = SUM(StandardWeight*(Num-ISNULL(RefundQty,0)))
FROM dbo.T_OMSOnlineOrderProduct t WITH(NOLOCK) WHERE OMSOnlineOrderId=@orderid AND t.RefundStatus<>6
SET @AllSku = SUBSTRING(@AllSku,0,255)
SET @allStoreSite=SUBSTRING(@allStoreSite,0,255)
update T_OMSOnlineOrder
set AllStoreSite=@AllStoreSite
where Id=@orderid
set @V_Count=@V_Count-1----防止死循环
end