[步骤]
消耗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)
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
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
=@Start and Num<>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
=@Start and Num
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;
[结果]
[期望]
优化SQL减少开销损耗