[步骤]
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempQ') and type='U') BEGIN DROP TABLE #tempQ END IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'tempdb..#tempAll') and type='U') BEGIN DROP TABLE #tempAll END select OrderSourceID, StatisticDate into #tempQ from RB_Statistic_Queue(NOLOCK)q where q.CustomerID=1 and StatisticType='OrderProfitStaDimDay' and IsProcess = 0 and ProcessMessage='1' ;CREATE INDEX IX_TempTable_ID ON #tempQ (OrderSourceID,StatisticDate);select g1.* into #tempAll From( select 1 CustomerID, '' HashKey,Getdate()AddTime,0 AllAmountRMB, tt1.是否区分发票 PromotionType,convert(date,tt1.DataDate)DataDate,1 DataDim,tt1.BusinessAdminID,tt1.BusinessTeamID,tt1.SalesAdminID,tt1.SalesTeamID,tt1.SettlementDate,tt1.OrderSourceType,tt1.OrderSourceID,tt1.AddOrderReasonType,tt1.WareHouseID,tt1.DeliveryTypeDsec,isnull(tt1.AliExpressTrusteeType,8)AliExpressTrusteeType, tt1.OrderSourceContry, sum(isnull(二毛USD,0))'ProfitsUSD', --二毛USD sum(isnull(二毛RMB,0))'ProfitsRMB',-- 二毛RMB sum(isnull(二点五毛USD,0))'ProfitsTwoFiveUSD',-- 二点五毛USD sum(isnull(二点五毛RMB,0))'ProfitsTwoFiveRMB',-- 二点五毛RMB sum(isnull(销售额USD,0))'SalesPriceUSD',-- 销售额USD sum(isnull(销售额RMB,0))'SalesPriceRMB',-- 销售额RMB sum(isnull(销售收入USD,0))'SalesIncomeUSD',-- 销售收入USD sum(isnull(运费收入USD,0))'TransportPayUSD',-- 运费收入USD sum(isnull(退款金额USD,0))'SalesRefundsUSD',-- 退款金额USD sum(isnull(折扣USD,0))'PromotionRebatesUSD',-- 折扣USD sum(isnull(其他收入USD,0))'OtherCreditsUSD',-- 其他收入USD sum(isnull(税费收入USD,0))'TaxUSD',-- 税费收入USD sum(isnull(销售收入RMB,0))'SalesIncomeRMB',-- 销售收入RMB sum(isnull(运费收入RMB,0))'TransportPayRMB',-- 运费收入RMB sum(isnull(退款金额RMB,0))'SalesRefundsRMB',-- 退款金额RMB sum(isnull(折扣RMB,0))'PromotionRebatesRMB',-- 折扣RMB sum(isnull(其他收入RMB,0))'OtherCreditsRMB',-- 其他收入RMB sum(isnull(税费收入RMB,0))'TaxRMB',-- 税费收入RMB sum(isnull(佣金USD,0))'CommisionFeeUSD',-- 佣金USD sum(isnull(FBA操作费USD,0))'FBATransactionFeesUSD',-- FBA操作费USD sum(isnull(平台其他支出USD,0))'OrderSourceOtherDebitsUSD',-- 平台其他支出USD sum(isnull(广告费USD,0))'CostOfAdvertisingUSD',-- 广告费USD sum(isnull(采购成本USD,0))'BuyPriceUSD',-- 采购成本USD sum(isnull(退还税费USD,0))'TaxRefundFeeUSD',-- 退还税费USD sum(isnull(包装成本USD,0))'PackagedCostUSD',-- 包装成本USD sum(isnull(补贴调整USD,0))'SubsidyPriceUSD',-- 补贴调整USD sum(isnull(本地仓运费USD,0))'PackageFeesUSD',-- 本地仓运费USD sum(isnull(海外仓运费USD,0))'OverSeasShipFeesUSD',-- 海外仓运费USD sum(isnull(头程运费USD,0))'FirstLegFeesUSD',-- 头程运费USD sum(isnull(仓库处理成本USD,0))'WareHouseManagementPriceUSD',-- 仓库处理成本USD sum(isnull(税费USD,0))'TariffFeesUSD',-- 税费USD sum(isnull(汇率折损USD,0))'ExchangeLostUSD',-- 汇率折损USD sum(isnull(海外仓仓租USD,0))'CustomStorageFeeUSD',-- 海外仓仓租USD sum(isnull(人工导入费USD,0))'ImportFeeUSD',-- 人工导入费USD sum(isnull(报损费USD,0))'BreakageFeeUSD',--报损费USD sum(isnull(佣金RMB,0))'CommisionFeeRMB',-- 佣金RMB sum(isnull(FBA操作费RMB,0))'FBATransactionFeesRMB',-- FBA操作费RMB sum(isnull(平台其他支出RMB,0))'OrderSourceOtherDebitsRMB',-- 平台其他支出RMB sum(isnull(广告费RMB,0))'CostOfAdvertisingRMB',-- 广告费RMB sum(isnull(采购成本RMB,0))'BuyPriceRMB',-- 采购成本RMB sum(isnull(退还税费RMB,0))'TaxRefundFeeRMB',-- 退还税费RMB sum(isnull(包装成本RMB,0))'PackagedCostRMB',-- 包装成本RMB sum(isnull(补贴调整RMB,0))'SubsidyPriceRMB',-- 补贴调整RMB sum(isnull(本地仓运费RMB,0))'PackageFeesRMB',-- 本地仓运费RMB sum(isnull(海外仓运费RMB,0))'OverSeasShipFeesRMB',-- 海外仓运费RMB sum(isnull(头程运费RMB,0))'FirstLegFeesRMB',-- 头程运费RMB sum(isnull(仓库处理成本RMB,0))'WareHouseManagementPriceRMB',-- 仓库处理成本RMB sum(isnull(税费RMB,0))'TariffFeesRMB',-- 税费RMB sum(isnull(汇率折损RMB,0))'ExchangeLostRMB',-- 汇率折损RMB sum(isnull(海外仓仓租RMB,0))'CustomStorageFeeRMB',-- 海外仓仓租RMB sum(isnull(人工导入费RMB,0))'ImportFeeRMB',-- 人工导入费RMB sum(isnull(报损费RMB,0))'BreakageFeeRMB',-- 报损费RMB, sum(isnull(AffiliatePartnerCommissionFeeRMB,0))'AffiliatePartnerCommissionFeeRMB', sum(isnull(AffiliatePartnerCommissionFeeUSD,0))'AffiliatePartnerCommissionFeeUSD', sum(isnull(SalesRefundsV2RMB,0))'SalesRefundsV2RMB', sum(isnull(SalesRefundsV2USD,0))'SalesRefundsV2USD', sum(isnull(SalesVatFeeRMB,0))'SalesVatFeeRMB', sum(isnull(SalesVatFeeUSD,0))'SalesVatFeeUSD', sum(isnull(CustomFeeOtherCostPriceRMB,0))'CustomFeeOtherCostPriceRMB', sum(isnull(CustomFeeOtherCostPriceUSD,0))'CustomFeeOtherCostPriceUSD', sum(FirstLegBreakageFeeRMB)FirstLegBreakageFeeRMB, sum(FirstLegBreakageFeeUSD)FirstLegBreakageFeeUSD, sum(OrderNum)OrderNum, sum(ProdutNum)ProdutNum , sum(isnull(tt1.退款商品数量,0))RefundsProductNum , tt1.Idx From ( select 0'是否区分发票',convert(varchar(10), alias.ShipDate,120) DataDate,alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0)SalesAdminID,ISNULL(alias.SalesTeamID,0)SalesTeamID,convert(varchar(10),alias.SettlementDate,120)SettlementDate,alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end AddOrderReasonType, alias.WareHouseID,ISNULL(alias.DeliveryTypeDsec, '')DeliveryTypeDsec, 3 AliExpressTrusteeType, alias.OrderSourceContry, sum(Profits/alias.USD_ExchangeRMB)'二毛USD', sum(Profits)'二毛RMB', sum((Profits+isnull(BreakageFee,0))/alias.USD_ExchangeRMB)'二点五毛USD', sum(Profits+isnull(BreakageFee,0))'二点五毛RMB', sum(0)'订单数', sum(SalesPrice/alias.USD_ExchangeRMB)'销售额USD', sum(SalesPrice)'销售额RMB', sum(0)'销售数量', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0)/alias.USD_ExchangeRMB)'销售收入USD', sum(alias.TransportPay/alias.USD_ExchangeRMB)'运费收入USD', sum(alias.SalesRefunds/alias.USD_ExchangeRMB)'退款金额USD', sum(alias.PromotionRebates/alias.USD_ExchangeRMB)'折扣USD', sum(alias.OtherCredits/alias.USD_ExchangeRMB)'其他收入USD', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0))/alias.USD_ExchangeRMB)'税费收入USD', sum(isnull(alias.SalesPrice+alias.TransportPay+alias.SalesRefunds+alias.PromotionRebates+alias.OtherCredits+(ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)),0))'销售收入RMB', sum(alias.TransportPay)'运费收入RMB', sum(alias.SalesRefunds)'退款金额RMB', sum(alias.PromotionRebates)'折扣RMB', sum(alias.OtherCredits)'其他收入RMB', sum((ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0)))'税费收入RMB', sum(alias.CommisionFee/alias.USD_ExchangeRMB)'佣金USD', sum(alias.FBATransactionFees/alias.USD_ExchangeRMB)'FBA操作费USD', sum(alias.OrderSourceOtherDebits/alias.USD_ExchangeRMB)'平台其他支出USD', sum(alias.CostOfAdvertising/alias.USD_ExchangeRMB)'广告费USD', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0))/alias.USD_ExchangeRMB)'采购成本USD', sum(ISNULL(alias.TaxRefundFee,0)/alias.USD_ExchangeRMB) '退还税费USD', sum(alias.PackagedCost/alias.USD_ExchangeRMB)'包装成本USD', sum(ISNULL(alias.SubsidyPrice,0)/alias.USD_ExchangeRMB)'补贴调整USD', sum(alias.PackageFees/alias.USD_ExchangeRMB)'本地仓运费USD', sum(alias.OverSeasShipFees/alias.USD_ExchangeRMB)'海外仓运费USD', sum(alias.FirstLegFees/alias.USD_ExchangeRMB)'头程运费USD', sum(alias.WareHouseManagementPrice/alias.USD_ExchangeRMB)'仓库处理成本USD', sum(alias.TariffFees/alias.USD_ExchangeRMB)'税费USD', sum(alias.ExchangeLost/alias.USD_ExchangeRMB)'汇率折损USD', sum(ISNULL(alias.CustomStorageFee,0)/alias.USD_ExchangeRMB)'海外仓仓租USD', sum(ISNULL(alias.ImportFee,0)/alias.USD_ExchangeRMB)'人工导入费USD', sum(ISNULL(alias.BreakageFee,0)/alias.USD_ExchangeRMB)'报损费USD', sum(alias.CommisionFee)'佣金RMB', sum(alias.FBATransactionFees)'FBA操作费RMB', sum(alias.OrderSourceOtherDebits)'平台其他支出RMB', sum(alias.CostOfAdvertising)'广告费RMB', sum((alias.BuyPrice+ISNULL(alias.DistributionPrice,0)))'采购成本RMB', sum(ISNULL(alias.TaxRefundFee,0)) '退还税费RMB', sum(alias.PackagedCost)'包装成本RMB', sum(ISNULL(alias.SubsidyPrice,0))'补贴调整RMB', sum(alias.PackageFees)'本地仓运费RMB', sum(alias.OverSeasShipFees)'海外仓运费RMB', sum(alias.FirstLegFees)'头程运费RMB', sum(alias.WareHouseManagementPrice)'仓库处理成本RMB', sum(alias.TariffFees)'税费RMB', sum(alias.ExchangeLost)'汇率折损RMB', sum(ISNULL(alias.CustomStorageFee,0))'海外仓仓租RMB', sum(ISNULL(alias.ImportFee,0))'人工导入费RMB', sum(ISNULL(alias.BreakageFee,0))'报损费RMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0))'AffiliatePartnerCommissionFeeRMB', sum(ISNULL(alias.AffiliatePartnerCommissionFee,0)/alias.USD_ExchangeRMB)'AffiliatePartnerCommissionFeeUSD', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END))) 'SalesRefundsV2RMB', ABS(SUM((CASE WHEN ISNULL(alias.SalesRefunds,0)=0 THEN 0 ELSE ISNULL(alias.SalesRefunds,0)+ISNULL(alias.PromotionRebates,0)+ISNULL(alias.OtherCredits,0)+ISNULL(alias.Tax,0)+ISNULL(alias.MarketplaceFacilitatorTax,0) END)/alias.USD_ExchangeRMB)) 'SalesRefundsV2USD', sum(ISNULL(alias.SalesVatFee,0))'SalesVatFeeRMB', sum(ISNULL(alias.SalesVatFee,0)/alias.USD_ExchangeRMB)'SalesVatFeeUSD', sum(ISNULL(alias.CustomFeeOtherCostPrice,0))'CustomFeeOtherCostPriceRMB', sum(ISNULL(alias.CustomFeeOtherCostPrice,0)/alias.USD_ExchangeRMB)'CustomFeeOtherCostPriceUSD', sum(alias.FirstLegBreakageFee)'FirstLegBreakageFeeRMB', sum(alias.FirstLegBreakageFee/alias.USD_ExchangeRMB)'FirstLegBreakageFeeUSD', COUNT(distinct alias.OrderCode)OrderNum, SUM(CASE WHEN alias.SalesPrice!=0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)ProdutNum , SUM(CASE WHEN ISNULL(alias.SalesRefunds,0)<>0 THEN alias.ProductNum+alias.ProdutNumUpdate ELSE 0 END)退款商品数量, alias.Idx From( select alias.*,3 AliExpressTrusteeType, case (alias.OrderCode) when '' then 0 else case when alias.SalesRefunds=0 then ROW_NUMBER () OVER (PARTITION BY alias.OrderCode ORDER BY alias.BusinessAdminID DESC,alias.SalesRefunds DESC) else 0 end end Idx from RB_Report_OrderSettlement alias(nolock) join #tempQ(NOLOCK) q on q.OrderSourceID=alias.OrderSourceID and q.StatisticDate=alias.ShipDate where alias.CustomerID=1 and alias.OrderSourceID in(select OrderSourceID from #tempQ) and alias.OrderSourceType in(1) AND (alias.OrderSourceType!=1 or ( alias.OrderSourceType=1 and (alias.CostOfAdvertising<>0 and alias.SettlementDate<'2024-05-01' ) ) or alias.CostOfAdvertising=0 OR alias.OrderDescription LIKE '%发票广告费%' ) )alias group by alias.OrderSourceType,alias.OrderSourceID,case when ISNULL(alias.AddOrderReasonType,0)=21 then '是' else '否' end,alias.WareHouseID, ISNULL(alias.DeliveryTypeDsec, ''), alias.OrderSourceContry,convert(varchar(10), alias.ShipDate,120),alias.BusinessAdminID,alias.BusinessTeamID,ISNULL(alias.SalesAdminID,0),ISNULL(alias.SalesTeamID,0),convert(varchar(10),alias.SettlementDate,120),alias.Idx )tt1 group by tt1.是否区分发票,tt1.DataDate,tt1.BusinessAdminID,tt1.BusinessTeamID,tt1.SalesAdminID,tt1.SalesTeamID,tt1.SettlementDate,tt1.OrderSourceType,tt1.OrderSourceID,tt1.AddOrderReasonType,tt1.WareHouseID,tt1.DeliveryTypeDsec,tt1.AliExpressTrusteeType, tt1.OrderSourceContry,tt1.Idx )g1 drop table if exists #deleteids; select alias.ID INTO #deleteids From RB_Report_OrderSettlement_StatShiptimeDay alias (NOLOCK) join #tempQ tmp on alias.OrderSourceID=tmp.OrderSourceID and alias.DataDate=tmp.StatisticDate and alias.PromotionType in(0); DROP TABLE IF EXISTS #tempids; DECLARE @leaveCount INT = 1; select @leaveCount=Count(1) from #deleteids (nolock) WHILE @leaveCount>0 BEGIN DROP TABLE IF EXISTS #tempids ; select top(10000)ID into #tempids from #deleteids (nolock) ; delete from RB_Report_OrderSettlement_StatShiptimeDay where ID in( select ID from #tempids ) delete from #deleteids where ID in( select ID from #tempids ) -- PRINT 'leave ' +convert(varchar(50), @leaveCount); select @leaveCount=Count(1) from #deleteids (nolock) WAITFOR DELAY '00:00:01.00' ; END;;drop table if exists #tempAllV2; select ROW_NUMBER() OVER(Order BY CustomerID)NumIdx,* INTO #tempAllV2 FROM #tempAll drop table if exists #tempAll; CREATE INDEX tmpidx20260513084113 ON #tempAllV2 (NumIdx) DROP TABLE IF EXISTS #tempidsV2; DECLARE @leaveCountV2 INT = 1; select @leaveCountV2=Count(1) from #tempAllV2 (nolock) WHILE @leaveCountV2>0 BEGIN DROP TABLE IF EXISTS #tempidsV2; select top(10000)NumIdx into #tempidsV2 from #tempAllV2 (nolock) ; insert into RB_Report_OrderSettlement_StatShiptimeDay(CustomerID,HashKey,AddTime,AllAmountRMB, PromotionType,DataDate,DataDim,BusinessAdminID,BusinessTeamID,SalesAdminID,SalesTeamID,SettlementDate,OrderSourceType,OrderSourceID,AddOrderReasonType,WareHouseID,DeliveryTypeDsec,AliExpressTrusteeType,OrderSourceContry,ProfitsUSD,ProfitsRMB,ProfitsTwoFiveUSD,ProfitsTwoFiveRMB,SalesPriceUSD,SalesPriceRMB,SalesIncomeUSD,TransportPayUSD,SalesRefundsUSD,PromotionRebatesUSD,OtherCreditsUSD,TaxUSD,SalesIncomeRMB,TransportPayRMB,SalesRefundsRMB,PromotionRebatesRMB,OtherCreditsRMB,TaxRMB,CommisionFeeUSD,FBATransactionFeesUSD,OrderSourceOtherDebitsUSD,CostOfAdvertisingUSD,BuyPriceUSD,TaxRefundFeeUSD,PackagedCostUSD,SubsidyPriceUSD,PackageFeesUSD,OverSeasShipFeesUSD,FirstLegFeesUSD,WareHouseManagementPriceUSD,TariffFeesUSD,ExchangeLostUSD,CustomStorageFeeUSD,ImportFeeUSD,BreakageFeeUSD,CommisionFeeRMB,FBATransactionFeesRMB,OrderSourceOtherDebitsRMB,CostOfAdvertisingRMB,BuyPriceRMB,TaxRefundFeeRMB,PackagedCostRMB,SubsidyPriceRMB,PackageFeesRMB,OverSeasShipFeesRMB,FirstLegFeesRMB,WareHouseManagementPriceRMB,TariffFeesRMB, ExchangeLostRMB,CustomStorageFeeRMB,ImportFeeRMB,BreakageFeeRMB,AffiliatePartnerCommissionFeeRMB,AffiliatePartnerCommissionFeeUSD,SalesRefundsV2RMB,SalesRefundsV2USD,SalesVatFeeRMB,SalesVatFeeUSD,CustomFeeOtherCostPriceRMB,CustomFeeOtherCostPriceUSD,FirstLegBreakageFeeRMB,FirstLegBreakageFeeUSD,OrderNum,ProdutNum,RefundsProductNum,Idx ) select CustomerID,HashKey,AddTime,AllAmountRMB, PromotionType,DataDate,DataDim,BusinessAdminID,BusinessTeamID,SalesAdminID,SalesTeamID,SettlementDate,OrderSourceType,OrderSourceID,AddOrderReasonType,WareHouseID,DeliveryTypeDsec,AliExpressTrusteeType,OrderSourceContry,ProfitsUSD,ProfitsRMB,ProfitsTwoFiveUSD,ProfitsTwoFiveRMB,SalesPriceUSD,SalesPriceRMB,SalesIncomeUSD,TransportPayUSD,SalesRefundsUSD,PromotionRebatesUSD,OtherCreditsUSD,TaxUSD,SalesIncomeRMB,TransportPayRMB,SalesRefundsRMB,PromotionRebatesRMB,OtherCreditsRMB,TaxRMB,CommisionFeeUSD,FBATransactionFeesUSD,OrderSourceOtherDebitsUSD,CostOfAdvertisingUSD,BuyPriceUSD,TaxRefundFeeUSD,PackagedCostUSD,SubsidyPriceUSD,PackageFeesUSD,OverSeasShipFeesUSD,FirstLegFeesUSD,WareHouseManagementPriceUSD,TariffFeesUSD,ExchangeLostUSD,CustomStorageFeeUSD,ImportFeeUSD,BreakageFeeUSD,CommisionFeeRMB,FBATransactionFeesRMB,OrderSourceOtherDebitsRMB,CostOfAdvertisingRMB,BuyPriceRMB,TaxRefundFeeRMB,PackagedCostRMB,SubsidyPriceRMB,PackageFeesRMB,OverSeasShipFeesRMB,FirstLegFeesRMB,WareHouseManagementPriceRMB,TariffFeesRMB, ExchangeLostRMB,CustomStorageFeeRMB,ImportFeeRMB,BreakageFeeRMB,AffiliatePartnerCommissionFeeRMB,AffiliatePartnerCommissionFeeUSD,SalesRefundsV2RMB,SalesRefundsV2USD,SalesVatFeeRMB,SalesVatFeeUSD,CustomFeeOtherCostPriceRMB,CustomFeeOtherCostPriceUSD,FirstLegBreakageFeeRMB,FirstLegBreakageFeeUSD,OrderNum,ProdutNum,RefundsProductNum,Idx From #tempAllV2 where NumIdx in( select NumIdx from #tempidsV2 ); delete from #tempAllV2 where NumIdx in( select NumIdx from #tempidsV2 ) -- PRINT 'leave ' +convert(varchar(50), @leaveCountV2); select @leaveCountV2=Count(1) from #tempAllV2 (nolock) WAITFOR DELAY '00:00:01.00'; END; drop table if exists #tempAllV2; drop table if exists #tempQ;
[结果]
[期望]
delete from RB_Report_OrderSettlement_StatShiptimeDay where ID in( select ID from #tempids
卡 再该语句 建议 分批次 关联删除 每次删除1万条
上次开发优化后现逻辑已经是10000/批次删除了