[步骤]
GetSingle:SELECT COUNT(1) FROM ( SELECT RB_Product_Supplier.SupplierType, RB_Product_Supplier.IsFBABarCode, RB_Procurement_Detail_SingleList.ID AS xdID, RB_Procurement_Detail_SingleList.Memo, RB_Procurement_Detail_SingleList.StepRemark, RB_Procurement_Detail_SingleList.IsNewFlow, RB_Procurement_Detail_SingleList.StepType, RB_Procurement_Detail_SingleList.Single_NO AS Single_NO, RB_Procurement_Detail_SingleList.AddTime AS AddTime, RB_Procurement_Detail_SingleList.FinishDate AS FinishDate, RB_Procurement_Detail_SingleList.ShipAmount AS ShipAmount, RB_Procurement_Detail_SingleList.StepState AS StepState, RB_Procurement_Detail_SingleList.SingleAmount AS SingleAmount, RB_Procurement_Detail_SingleList.TrackingNumber, RB_Procurement_Detail_SingleList.TrackingNumbers, RB_Procurement_Detail_SingleList.BuyerID, RB_Procurement_Detail_SingleList.ProcPayType, RB_Procurement_Detail_SingleList.TransportType, RB_Procurement_Detail_SingleList.DeliveryTime, RB_Procurement_Detail_SingleList.PayState, RB_Procurement_Detail_SingleList.OrderStatus, RB_Procurement_Detail_SingleList.EnquiryStates, RB_Procurement_Detail_SingleList.IsAlibabaSingle, RB_Procurement_Detail_SingleList.IsTaobaoSingle, RB_Procurement_Detail_SingleList.IsAutoSubmit, RB_Procurement_Detail_SingleList.AutoSubmitResultType, RB_Procurement_Detail_SingleList.IsAuto, RB_Procurement_Detail_SingleList.AutoSubmitResultText, RB_Procurement_Detail_SingleList.PrepareBuyTime, RB_Procurement_Detail_SingleList_ForeignCurrency.Currency, RB_Procurement_Detail_SingleList_ForeignCurrency.ExchangeRMB, RB_Procurement_Detail_SingleList_ForeignCurrency.SingleAmountForeign, RB_Procurement_Detail_SingleList_ForeignCurrency.MinusAmountCurrencyForeign, RB_Procurement_Detail_SingleList_ForeignCurrency.ShipAmountForeign, RB_Procurement_Detail_SingleList_ForeignCurrency.PayTotalAmountForeign, RB_Procurement_Detail_SingleList_ForeignCurrency.PaidAmountForeign, WareHouseName AS WareHouseName, RB_Procurement_List.WareHouseID, RB_Procurement_List.ID, RB_Procurement_List.FinishStatus, (select top 1 SupplierSource from RB_Product_Supplier(nolock) where ID = RB_Procurement_Detail_SingleList.SupplierID ) AS SupplierSource, RB_WareHouse.IsTaxFree, RB_WareHouse.WareHouseAddress, RB_WareHouse.WareHouseCode, RB_WareHouse.WareHouseProvince, RB_WareHouse.WareHouseCity, RB_WareHouse.WareHousePostCode, RB_Procurement_List.AOrderCode, RB_Procurement_List.P_Code AS P_Code, RB_Procurement_List.BuyerName AS BuyerName, RB_Procurement_Detail_SingleList.SupplierName, RB_Procurement_Detail_SingleList.IsNewSupplier, RB_Procurement_List.DevelopAdminName AS DevelopAdminName, RB_Procurement_List.IsUrgent, RB_Procurement_List.IsFactoryDirectDeliveryOrder, vNba.NobuyAmount, ROW_NUMBER() OVER (ORDER BY RB_Procurement_Detail_SingleList.ID DESC) AS RowNo, RB_Procurement_Detail_SingleList.OrderNo, RB_Procurement_List.IsSample FROM RB_Procurement_Detail_SingleList WITH (NOLOCK) LEFT JOIN RB_Procurement_Detail_SingleList_ForeignCurrency WITH (NOLOCK) ON RB_Procurement_Detail_SingleList_ForeignCurrency.Single_NO = RB_Procurement_Detail_SingleList.Single_NO AND RB_Procurement_Detail_SingleList_ForeignCurrency.CustomerID = RB_Procurement_Detail_SingleList.CustomerID LEFT JOIN RB_Procurement_List WITH (NOLOCK) ON RB_Procurement_List.P_Code = RB_Procurement_Detail_SingleList.P_Code AND RB_Procurement_List.CustomerID = RB_Procurement_Detail_SingleList.CustomerID LEFT JOIN RB_Product_Supplier WITH (NOLOCK) ON RB_Procurement_List.SupplierID = RB_Product_Supplier.ID AND RB_Procurement_List.CustomerID = RB_Product_Supplier.CustomerID LEFT JOIN E_Website (NOLOCK) ON E_Website.CustomerID = RB_Procurement_List.CustomerID LEFT JOIN RB_WareHouse (NOLOCK) ON RB_Procurement_List.WareHouseID = RB_WareHouse.ID AND RB_Procurement_List.CustomerID = RB_WareHouse.CustomerID outer apply( SELECT COUNT(1) AS NobuyAmount FROM ( SELECT pdsd.ActualBuyNum AS SingleNum , buy.ActualBuyNum , pdsd.ActualBuyNum - ISNULL(buy.ActualBuyNum, 0) AS NobuyNum FROM RB_Procurement_Detail_SingleList_Detail pdsd WITH ( NOLOCK ) LEFT JOIN RB_Procurement_Detail_SingleList pds WITH(NOLOCK) ON pds.CustomerID = pdsd.CustomerID AND pds.ID = pdsd.ListID LEFT JOIN ( SELECT pdb.Single_NO , pdb.CustomerID , pdbd.SKU , pdbd.DetailID , SUM(pdbd.ActualBuyNum) AS ActualBuyNum FROM RB_Procurement_Detail_BuyList_Detail pdbd ( NOLOCK ) LEFT JOIN dbo.RB_Procurement_Detail_BuyList pdb ( NOLOCK ) ON pdb.CustomerID = pdbd.CustomerID AND pdbd.ListNo = pdb.ListNo GROUP BY pdb.Single_NO , pdb.CustomerID , pdbd.SKU , pdbd.DetailID ) buy ON buy.CustomerID = pdsd.CustomerID AND buy.DetailID = pdsd.DetailID AND buy.SKU = pdsd.SKU AND buy.Single_NO = pds.Single_NO WHERE pds.Single_NO = RB_Procurement_Detail_SingleList.Single_NO and pds.CustomerID = RB_Procurement_Detail_SingleList.CustomerID ) t WHERE t.NobuyNum > 0 )vNba WHERE RB_Procurement_Detail_SingleList.CustomerID = 1 and RB_Procurement_Detail_SingleList.StepState=9 and RB_Procurement_Detail_SingleList.IsNewFlow=1 ) AS PagerTable
[结果]
[期望]
从业务角度加入核心条件 或者改造SQL 使其效率提升