這是實際場景當中的一個例子，拿出來分析總結思路。
-- SQL 查詢 --
SELECT orderQuery.Rk_SkuCode ,
orderQuery.SkuName,
SUM(ISNULL(orderQuery.OrderTotal, 0))
- SUM(ISNULL(removeQuery.RemoveTotal, 0))
- SUM(ISNULL(pickQuery.PickTotal, 0))
- SUM(ISNULL(tempPickQuery.TempPickTotal, 0)) AS RealOrders ,
SUM(ISNULL(orderQuery.OrderTotal, 0)) AS Orders ,
SUM(ISNULL(removeQuery.RemoveTotal, 0)) AS Removes ,
SUM(ISNULL(pickQuery.PickTotal, 0)) AS Picks ,
SUM(ISNULL(stockQuery.StockTotal, 0)) AS Stocks ,
SUM(ISNULL(tempPickQuery.TempPickTotal, 0)) AS TempPicks
FROM ( SELECT Rk_SkuCode , do.Rk_SkuName AS SkuName,
SUM(do.UnitQty) AS OrderTotal
FROM dbo.SALE_Bill_Sale_Order o ,
dbo.SALE_Bill_Sale_Order_Detail do
WHERE o.Fk_ATS_Wfs_NodeId != 100100170
AND o.Fk_BillTypeId = 100100010
AND o.Pk_OrderId = do.Fk_OrderId
AND o.Fk_WarehouseId = 140416154556733
GROUP BY Rk_SkuCode,do.Rk_SkuName
) orderQuery
LEFT OUTER JOIN ( SELECT Rk_SkuCode ,
SUM(rd.Quantity) AS RemoveTotal
FROM dbo.WMS_Bill_Remove r ,
dbo.WMS_Bill_Remove_Detail rd
WHERE r.Pk_RemoveId = rd.Fk_RemoveId
AND r.Fk_WarehouseId = 140416154556733
AND r.Fk_BillTypeId = 100100110
AND r.Fk_ATS_Wfs_NodeId = 100300140
GROUP BY Rk_SkuCode
) removeQuery ON orderQuery.Rk_SkuCode = removeQuery.Rk_SkuCode
SUM(s.Quantity) AS PickTotal
FROM dbo.WMS_Stock s ,
dbo.WMS_Info_PartArea p
WHERE p.Pk_PartAreaId = s.Fk_PartAreaId
AND s.Fk_WarehouseId = 140416154556733
AND p.Ek_Type = 100100120
) pickQuery ON orderQuery.Rk_SkuCode = pickQuery.Rk_SkuCode
SUM(s.Quantity) AS StockTotal
AND p.Ek_Type = 100100150
) stockQuery ON orderQuery.Rk_SkuCode = stockQuery.Rk_SkuCode
SUM(s.Quantity) AS TempPickTotal
FROM dbo.WMS_Stock s
WHERE s.Fk_WarehouseId = 140416154556733
AND s.Fk_LocationId = 140612114677165
) tempPickQuery ON orderQuery.Rk_SkuCode = tempPickQuery.Rk_SkuCode
GROUP BY orderQuery.Rk_SkuCode,orderQuery.SkuName
HAVING ( SUM(ISNULL(orderQuery.OrderTotal, 0))
- SUM(ISNULL(removeQuery.RemoveTotal, 0))
- SUM(ISNULL(pickQuery.PickTotal, 0))
- SUM(ISNULL(tempPickQuery.TempPickTotal, 0)) ) > 0
作者：Stephen Cui
出處：http://www.cnblogs.com/cuiyansong
版權聲明：文章屬于本人及部落格園共有，凡是沒有标注[轉載]的，請在文章末尾加入我的部落格位址。
如果您覺得文章寫的還不錯，請點選“推薦一下”，謝謝。