22,209
社区成员
发帖
与我相关
我的任务
分享
UPDATE A
-- 任意一个值为 NULL,则整个结果为 NULL, 如果要避免,则使用 ISNULL 做处理
SET InStoreNum = ISNULL(B.StoreNum,0) + ISNULL(C.inNum,0) - ISNULL(D.OutNum,0)
FROM #tb AS A
-- 你原来的匹配关系,要求 GoodsID 在所有表中都有才更新,如果任何一个表中存在都更新,则入用 LEFT JOIN
LEFT JOIN (
SELECT goodsID,
StoreNum
FROM T_CheckStore
WHERE CONVERT (VARCHAR (10), fdate, 120) = CONVERT (VARCHAR (10), @NCDate)
AND DepID = @DepID
AND iFlag = 0
) AS B ON A.GoodsID = b.GoodsID
LEFT JOIN (
SELECT p.GoodsID,
SUM(p.num) AS inNum
FROM Log_Store AS p
WHERE p.iFlag = 0
AND p.depID = @depID
AND CONVERT (VARCHAR (10), p.fDate, 120) > CONVERT (VARCHAR (10), @YCDate, 120)
AND CONVERT (VARCHAR (10), p.fDate, 120) <= CONVERT (VARCHAR (10), @ViewDate, 120)
GROUP BY p.GoodsID
) AS C ON a.GoodsID = c.GoodsID
LEFT JOIN (
SELECT q.goodsid,
sum(q.num) AS OutNum
FROM log_Store AS q
WHERE (q.iflag = 1
AND q.depid = @DepID
AND CONVERT (VARCHAR (10), q.fdate, 120) > CONVERT (VARCHAR (10), @YCDate, 120)
AND CONVERT (VARCHAR (10), q.fdate, 120) <= CONVERT (VARCHAR (10), @ViewDate, 120))
GROUP BY q.goodsid
) AS D on a.GoodsID = d.GoodsID
update data set Num=data.Num+isnull(b.NumB,0) + isnull((select sum(NumC) from #tbC c where c.idA=data.idA),0)
from #tbA as data left join #tbB as b on data.idA=b.idA
where data.idA=1
UPDATE A SET tbA.Num=B.bValue+C.cValue
FROM tbA A,
( SELECT idA, bValue FROM tbB
--WHERE 条件
)B,
( SELECT idA, sum(tbC.cValue) as cValue FROM tbC
--WHERE 条件
GROUP BY idA
)C
WHERE A.idA = B.idA AND A.idA = C.idA
UPDATE A SET tbA.Num=tbB.bValue+( SELECT sum(tbC.cValue) FROM tbC C WHERE C.idA=a.idA)
FROM tbA A, tbB B
WHERE A.idA = B.idA