[步骤]

消耗CPU 最多 

DECLARE @CustomerID int

                            declare @Start int,@Limit int,@Total int

                            set @Start=1

                            set @Limit=10000

                            set @CustomerID=1 

IF OBJECT_ID('Tempdb..#tempSkuRange') IS NOT NULL

                        DROP TABLE #tempSkuRange;

                        IF OBJECT_ID('Tempdb..#temp_Procurement') IS NOT NULL

                        DROP TABLE #temp_Procurement;

                        IF OBJECT_ID('Tempdb..#tempActiveInfo') IS NOT NULL

                        DROP TABLE #tempActiveInfo;

                        IF OBJECT_ID('Tempdb..#tempSkuRangeOrder') IS NOT NULL

                        DROP TABLE #tempSkuRangeOrder;

create table #tempSkuRange(SKU varchar(50) COLLATE Chinese_PRC_CI_AS) 

                        create table #tempSkuRangeOrder(SKU varchar(50) COLLATE Chinese_PRC_CI_AS,Num int) 

                        create table #temp_Procurement(P_Code varchar(50) COLLATE Chinese_PRC_CI_AS,SKU varchar(30) COLLATE Chinese_PRC_CI_AS,SingleNum int,ActualBuyNum int,ProductNum int,PutNum int,ProcNum INT,WarehouseID INT)  

                        create table #tempActiveInfo(SKU varchar(50) COLLATE Chinese_PRC_CI_AS,PutTime datetime,ShipTime datetime,ActiveTime DATETIME,WarehouseID INT)

insert into #tempSkuRange select distinct SKU from RB_Warehouse_Product (nolock) where len(SKU)>0 and CustomerID=@CustomerID AND WareHouseID IN(SELECT id FROM dbo.RB_WareHouse (nolock) WHERE WareHouseType=1)

                            insert into #tempSkuRangeOrder(Num,SKU) SELECT ROW_NUMBER() OVER(order by SKU) AS Num,SKU from #tempSkuRange (nolock)

select @Total=COUNT(*) from #tempSkuRange

                            while @Start<=@Total

                            BEGIN

                            insert into #temp_Procurement(SKU,P_Code,SingleNum,ActualBuyNum,ProductNum,PutNum,ProcNum,WarehouseID)

                                                                    select SKU,P_Code,SingleNum,ActualBuyNum,ProductNum,PutNum,ProcNum,d.WareHouseID from (select  a.SKU,a.P_Code,a.SingleNum,a.ActualBuyNum,a.ProductNum,a.PutNum,(SELECT MAX(ProcNum)

                                                                    FROM ( VALUES ( SingleNum), ( ActualBuyNum),(ProductNum) ) AS ProcNum (ProcNum)) AS ProcNum,c.WareHouseID from RB_Procurement_Detail a (nolock)

                                                                    inner join RB_Procurement_List c (nolock) on a.CustomerID=c.CustomerID and a.P_Code=c.P_Code

                                                                    inner join (SELECT * from #tempSkuRangeOrder where Num>=@Start and Num<@Start+@Limit) b on a.SKU=b.SKU where a.CustomerID=@CustomerID and c.FinishStatus<>1

                                                                    union all

                                                                    select  a.SKU,a.P_Code,0 SingleNum,0 ActualBuyNum,0 ProductNum,0 PutNum,0 ProcNum,c.WareHouseID from RB_Procurement_Detail a (nolock)

                                                                    inner join RB_Procurement_List c (nolock) on a.CustomerID=c.CustomerID and a.P_Code=c.P_Code

                                                                    inner join (SELECT * from #tempSkuRangeOrder where Num>=@Start and Num<@Start+@Limit) b on a.SKU=b.SKU where a.CustomerID=@CustomerID and c.FinishStatus=1) d                         

                            set @Start=@Start+@Limit-1

                            END

   insert into #tempActiveInfo(SKU,PutTime,ShipTime,ActiveTime,WarehouseID)

                                                                    select isnull(p.SKU,q.SKU) SKU,p.PutTime,q.ShipTime,(SELECT MAX(ActiveTime)

                                                                    FROM (VALUES (PutTime), (ShipTime)) AS ActiveTime (ActiveTime)) AS ActiveTime,ISNULL(p.WareHouseID,q.WareHouseID) WareHouseID from 

                                                                    (select a.SKU,b.WareHouseID,MAX(a.AddTime) as PutTime from RB_Procurement_Detail_PutList a (nolock) INNER JOIN RB_Procurement_List b (NOLOCK) ON a.CustomerID=b.CustomerID AND a.P_Code=b.P_Code

                                                                    where IsDeleted=0 and a.CustomerID=@CustomerID group by a.SKU,b.WareHouseID) p

                                                                    full outer join

                                                                    (select b.SKU,a.WareHouseID,MAX(a.ShipTime) ShipTime from RB_Procurement_Order_Package a (nolock) inner join RB_Procurement_Order_Package_Detail b (nolock) on a.ID=b.PackageID 

                                                                    where a.CustomerID=@CustomerID and a.PackageState=3 group by a.WareHouseID,b.SKU) q on p.SKU=q.SKU AND p.WareHouseID=q.WareHouseID

 select p.SKU,p.WarehouseID,ISNULL(p.PutNum,0) PutNum,isnull(p.ProcessingNum,0) ProcessingNum,isnull(p.TotalProcNum,0) TotalProcNum,p.ActiveTime,p.PutTime,p.ShipTime from (

                                                        select isnull(t1.SKU,t2.SKU) SKU,ISNULL(t1.WarehouseID,t2.WarehouseID) WarehouseID,t1.PutNum,t1.ProcessingNum,t1.TotalProcNum,t2.ShipTime,t2.ActiveTime,t2.PutTime from 

                                                        (select SKU,WarehouseID,SUM(PutNum) PutNum,sum(ProcNum) TotalProcNum,(sum(ProcNum)-sum(PutNum)) ProcessingNum from #temp_Procurement group by WarehouseID,SKU) t1

                                                        full outer join #tempActiveInfo t2 on t1.SKU=t2.SKU AND t1.WarehouseID=t2.WarehouseID) p inner join #tempSkuRange q on p.SKU=q.SKU

DELETE a from RB_Warehouse_Product_ActiveInfo a where a.CustomerID=@CustomerID and not exists(select SKU from #tempActiveInfo b where a.SKU=b.SKU AND a.WarehouseID=b.WarehouseID)

IF OBJECT_ID('Tempdb..#tempSkuRange') IS NOT NULL

                            DROP TABLE #tempSkuRange;

                            IF OBJECT_ID('Tempdb..#temp_Procurement') IS NOT NULL

                            DROP TABLE #temp_Procurement;

                            IF OBJECT_ID('Tempdb..#tempActiveInfo') IS NOT NULL

                            DROP TABLE #tempActiveInfo;

                            IF OBJECT_ID('Tempdb..#tempSkuRangeOrder') IS NOT NULL

                            DROP TABLE #tempSkuRangeOrder;

[结果]

[期望]