首页 > 代码库 > SQL语句是以一个patch为单位编译
SQL语句是以一个patch为单位编译
如下语句如果不加go的话,当做一个批处理编译时会报错,提示没有split_count和split_amount列。加了go时可以一起执行
--3.Kit split in sale_detail: split_count,split_amount
ALTER TABLE sale_detail ADD split_count INT,split_amount float
go
--EXECUTE TIME 40 minute
UPDATE ssd SET ssd.split_count=tt.split_count,ssd.split_amount=tt.split_amount
FROM dbo.sale_detail 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 sale_detail 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
SQL语句是以一个patch为单位编译