[步骤]
(@CustomerID int,@AliExpressTrusteeType int,@tb_OrderIds [Tb_StringIds] READONLY)UPDATE alias SET alias.TotalPrice = T.ProductTotalPrice, alias.ProductTotalPrice = T.ProductTotalPrice,alias.UpdateTime = GETDATE() FROM RB_OrderInfo alias INNER JOIN ( SELECT o.OrderCode,SUM(O.ProductTotalCount * C.SupplyPrice) ProductTotalPrice FROM ( SELECT H.OrderSourceID,H.OrderCode, (L.ProductNum + L.ProductNumUpdate) ProductTotalCount, CASE WHEN CHARINDEX('"sku_id":"', L.ExtendedInfo) > 0 THEN SUBSTRING( L.ExtendedInfo, CHARINDEX('"sku_id":"', L.ExtendedInfo) + LEN('"sku_id":"'), CHARINDEX('"', L.ExtendedInfo, CHARINDEX('"sku_id":"', L.ExtendedInfo) + LEN('"sku_id":"')) - (CHARINDEX('"sku_id":"', L.ExtendedInfo) + LEN('"sku_id":"')) ) ELSE NULL END AS SkuId FROM dbo.RB_OrderInfo H WITH(NOLOCK) INNER JOIN dbo.RB_OrderSource OS WITH(NOLOCK) ON H.OrderSourceID = OS.ID INNER JOIN dbo.RB_OrderList L WITH(NOLOCK) ON H.OrderCode = L.OrderCode INNER JOIN @tb_OrderIds oi ON H.OrderCode = oi.Id WHERE H.CustomerID = @CustomerID AND H.AliExpressTrusteeType = @AliExpressTrusteeType --GROUP BY H.OrderCode ) O INNER JOIN RB_AliExpress_SkuEstimatedPrice C WITH(NOLOCK) ON O.OrderSourceID = C.OrderSourceID AND O.SkuId = c.SkuId GROUP BY o.OrderCode ) T ON alias.OrderCode = T.OrderCode
[结果]
[期望]
提升SQL性能，加快 更新速度