分分了,不过我还是自己解决了问题,看看我的代码:
SELECT DP.Depot, DP.ProductNo,
SUM(ISNULL(BB.Tot,0)+ISNULL(BI.Tot,0)-ISNULL(BIB.Tot,0)-ISNULL(BO.Tot,0)+ISNULL(BOB.Tot,0)+ISNULL(BC.Tot,0)-ISNULL(BD.Tot,0)) [BeforeStock],
SUM(ISNULL(CI.Tot,0)+ISNULL(COB.Tot,0)+ISNULL(CC.Tot,0)) AS CurrentII,
SUM(ISNULL(CIB.Tot,0)+ISNULL(CO.Tot,0)+ISNULL(CD.Tot,0)) AS CurrentOO,
SUM(ISNULL((ISNULL(BB.Tot,0)+ISNULL(BI.Tot,0)-ISNULL(BIB.Tot,0)-ISNULL(BO.Tot,0)+ISNULL(BOB.Tot,0)+ISNULL(BC.Tot,0)-ISNULL(BD.Tot,0)),0)+ISNULL(CI.Tot,0)-ISNULL(CIB.Tot,0)-ISNULL(CO.Tot,0)+ISNULL(COB.Tot,0)+ISNULL(CC.Tot,0)-ISNULL(CD.Tot,0)) CurrentStock
FROM dbo.PDepotProductAll DP
LEFT OUTER JOIN #TPDepotBeforeIn BI ON
DP.ProductNo = BI.ProductNo AND DP.Depot = BI.Depot
LEFT OUTER JOIN #TPDepotBeforeIn BIB ON
DP.ProductNo = BIB.ProductNo AND DP.Depot = BIB.Depot
LEFT OUTER JOIN #TPDepotBeforeOut BO ON
DP.ProductNo = BO.ProductNo AND DP.Depot = BO.Depot
LEFT OUTER JOIN #TPDepotBeforeOutBack BOB ON
DP.ProductNo = BOB.ProductNo AND DP.Depot = BOB.Depot
LEFT OUTER JOIN #TPDepotBeforeInc BC ON
DP.ProductNo = BC.ProductNo AND DP.Depot = BC.Depot
LEFT OUTER JOIN #TPDepotBeforeDec BD ON
DP.ProductNo = BD.ProductNo AND DP.Depot = BD.Depot
LEFT OUTER JOIN #TPDepotBeforeBase BB ON
DP.ProductNo = BB.ProductNo AND DP.Depot = BB.Depot
-----------------------------------------------------------
LEFT OUTER JOIN #TPDepotCurrentIn CI ON
DP.ProductNo = CI.ProductNo AND DP.Depot = CI.Depot
LEFT OUTER JOIN #TPDepotCurrentInBack CIB ON
DP.ProductNo = CIB.ProductNo AND DP.Depot = CIB.Depot
LEFT OUTER JOIN #TPDepotCurrentOut CO ON
DP.ProductNo = CO.ProductNo AND DP.Depot = CO.Depot
LEFT OUTER JOIN #TPDepotCurrentOutBack COB ON
DP.ProductNo = COB.ProductNo AND DP.Depot = COB.Depot
LEFT OUTER JOIN #TPDepotCurrentInc CC ON
DP.ProductNo = CC.ProductNo AND DP.Depot = CC.Depot
LEFT OUTER JOIN #TPDepotCurrentDec CD ON
DP.ProductNo = CD.ProductNo AND DP.Depot = CD.Depot
GROUP BY DP.Depot,DP.ProductNo
ORDER BY DP.Depot,DP.ProductNo