update 大表

--SELECT * FROM sale_detail sd WHERE split_count IS NOT NULL

--SELECT * FROM sale_detail sd WHERE split_amount IS NULL

SET NOCOUNT OFF

WHILE 1=1

BEGIN

WITH cte AS

(

SELECT TOP (100000) * FROM dbo.sale_detail WHERE split_count IS NULL OR split_amount IS NULL

 

)

UPDATE ssd SET ssd.split_count=tt.split_count,ssd.split_amount=tt.split_amount

FROM cte ssd

JOIN (

SELECT a.rid,SUM(ISNULL(a.split_units,0)) AS split_count,SUM(ISNULL(a.split_rmb,0)) AS split_amount

FROM (

SELECT sd.rid, SUM(( CASE WHEN pk.count IS NULL THEN 1 ELSE pk.count END ) * sd.count) AS split_units,

SUM(CASE WHEN pk.price IS NULL THEN sd.amount ELSE pk.price * pk.count * sd.count END ) AS split_rmb

FROM cte sd WITH(NOLOCK)

LEFT JOIN product p WITH(NOLOCK) ON p.product_id = sd.product_id

LEFT JOIN product_kit pk WITH(NOLOCK) ON pk.kit_id = p.product_id

AND pk.status = 0

AND sd.sale_date >= pk.start_date

AND sd.sale_date <= pk.end_date

WHERE sd.status = 0

AND p.status <> 1

AND p.ispromotion = 0

GROUP BY sd.rid

) a

GROUP BY a.rid

) tt ON ssd.rid = tt.rid

 IF @@ROWCOUNT<100000 BREAK;

end

转载于:https://www.cnblogs.com/dotagg/p/6372070.html