[步骤]
(@CustomerID int,@OrderSourceID int) SELECT alias.ID, alias.CustomerID, alias.OrderCode, alias.SKU, alias.SellerSKU, alias.GroupSKU, alias.ProductNum, alias.ProductNumUpdate, ISNULL(alias.GroupProductNum, 0) GroupProductNum, ISNULL(alias.GroupProductNumUpdate, 0) GroupProductNumUpdate, p.WithBattery, p.Pack_Length, p.Pack_Width, p.Pack_Height, p.GrossWeight, p.NetWeight, p.Length, p.Width, p.Height, ROW_NUMBER() OVER (PARTITION BY alias.GroupSKU, alias.SKU ORDER BY alias.ID) GroupNO FROM dbo.RB_OrderList (NOLOCK) alias INNER JOIN dbo.RB_OrderInfo (NOLOCK) oi ON oi.CustomerID = alias.CustomerID AND oi.OrderCode = alias.OrderCode INNER JOIN dbo.RB_OrderInfo_BeikuExpand (NOLOCK) oibe ON oibe.CustomerID = oi.CustomerID AND oibe.OrderCode = oi.OrderCode LEFT JOIN dbo.RB_WareHouse (NOLOCK) wh ON wh.CustomerID = oi.CustomerID AND oi.WareHouseID = wh.ID LEFT JOIN dbo.RB_Product (NOLOCK) p ON alias.CustomerID = p.CustomerID AND alias.SKU = p.SKU WHERE oi.CustomerID = @CustomerID AND ISNULL(oi.AmazonShipmentId, '') = '' AND oi.OrderSourceID = @OrderSourceID AND oi.OrderStatus IN ( 0, 1, 3 ) AND oibe.InterfaceType = 1 AND oibe.OrderBuildType = 1 -- AND ISNULL(oibe.OrderShipmentType, 0) != 1 -- AND wh.IsStockpile = 1 AND (alias.ProductNum + alias.ProductNumUpdate) > 0;
[结果]
[期望]
优化SQL （或者添加核心条件）提升查询效率
orderList = hp.GET<RB.DALExtendV3.RB_OrderListExtendV3>().GetFBAOrderModelList(customerId, model.OrderSourceID, model.OrderBuildType);