[步骤]
SELECT o.OrderSourceID AS orderSourceId, l.SellerSKU orderSourceSku, SUM ( l.ProductNum + l.ProductNumUpdate - Isnull( pod.ProductNum, 0 ) ) / Isnull( ( SELECT SUM ( SkuNum ) AS Expr1 FROM dbo.RB_Product_GroupSku AS g WITH ( NOLOCK ) WHERE g.CustomerID = o.CustomerID AND ( GroupSku = ( CASE WHEN ISnull( l.GroupSKU, '' ) = '' THEN l.SKU ELSE l.GroupSKU END ) ) ), 1 ) AS purchaseNum, o.ToWareHouseID as warehouseId, ( CASE WHEN ISnull(l.GroupSKU,'') = '' THEN l.SKU ELSE l.GroupSKU END ) as sku FROM dbo.RB_OrderInfo AS o WITH ( NOLOCK ) INNER JOIN dbo.RB_OrderSource s ON o.OrderSourceID = s.ID AND o.CustomerID = s.CustomerID INNER JOIN dbo.RB_OrderList AS l WITH ( NOLOCK ) ON o.OrderCode = l.OrderCode AND o.CustomerID = l.CustomerID LEFT JOIN dbo.RB_OrderInfo_BeikuExpand h WITH ( NOLOCK ) ON o.OrderCode = h.OrderCode AND o.CustomerID = h.CustomerID LEFT JOIN ( SELECT d.OrderCode, OrderListID, d.CustomerID, SUM ( ProductNum ) AS ProductNum FROM RB_Procurement_Order_Package p WITH ( NOLOCK ) INNER JOIN dbo.RB_Procurement_Order_Package_OrderDetail d WITH ( NOLOCK ) ON d.CustomerID = p.CustomerID AND d.PackageID = p.ID LEFT JOIN RB_OrderInfo o WITH ( nolock ) ON d.OrderCode = o.OrderCode AND d.CustomerID = o.CustomerID LEFT JOIN dbo.RB_OrderInfo_BeikuExpand h WITH ( NOLOCK ) ON o.OrderCode = h.OrderCode AND o.CustomerID = h.CustomerID WHERE p.PackageState = 3 AND o.AddTime > Dateadd( MONTH, - 6, Getdate( ) ) AND o.IsStockOrder = 1 AND h.orderBuildType IN ( 1, 2 ) AND o.OrderStatus = 3 AND o.OrderState < 2 GROUP BY d.OrderCode, OrderListID, d.CustomerID ) AS pod ON pod.OrderCode = l.OrderCode AND pod.CustomerID = l.CustomerID AND pod.OrderListID = l.ID WHERE l.ProductNum + l.ProductNumUpdate - Isnull( pod.ProductNum, 0 ) > 0 AND s.OrderSourceType = 1 AND o.OrderStatus = 3 AND o.OrderState < 2 AND o.IsStockOrder = 1 AND h.orderBuildType IN ( 1, 2 ) AND o.AddTime > Dateadd( MONTH, - 6, Getdate( ) ) GROUP BY o.ToWareHouseID, o.CustomerID, l.SellerSKU, o.OrderSourceID, CASE WHEN ISnull( l.GroupSKU, '' ) = '' THEN l.SKU ELSE l.GroupSKU END[结果]
[期望]
添加 条件 0.CustomerID =1