1 .问题分析
select count() 查询，将不必要的查询字段，函数计算，和left join 去掉
测试去掉后查询从180秒 到优化后的 1秒查询出结果
2.解决方案 参考（请分析业务逻辑，目前的原查询sql有有大量的函数统计，不必要的查询字段，left join 查询，建议去掉）
优化后的sql 如下：
SELECT Count(0)
FROM (SELECT Abs(Isnull(wk.OutPutNum, 0) - Abs(Isnull(pu.OperateNum, 0))) AS DifferenceNum
FROM (SELECT put.CustomerID,
put.WareHouseID,
put.OutPutNum,
put.SKU
FROM RB_WareHouse_Product_OutPut_Log put WITH(NOLOCK)
WHERE put.CustomerID = 1
AND put.OutTime >= '2022-03-28'
AND put.OutTime < Dateadd(DAY, 1, '2022-04-28')
AND put.WareHouseID IN ( 1100000, 2, 279, 333,
336, 687, 1185, 2205,
3216, 3584, 3922, 4316,
5312, 7056, 7057, 7085,
7556, 7590 )
GROUP BY put.CustomerID,
put.WareHouseID,
put.OutPutNum,
put.SKU) wk
FULL JOIN (SELECT inv.CustomerID,
inv.WareHouseID,
inv.OriginBillNumber,
inv.OperateNum,
inv.SKU
FROM RB_Inventory_DetailLog inv WITH(NOLOCK)
WHERE inv.BillType = 5
AND inv.CustomerID = 1
AND inv.OperateTime >= '2022-03-28'
AND inv.OperateTime < Dateadd(DAY, 1, '2022-04-28')
AND inv.WareHouseID IN ( 1100000, 2, 279, 333,
336, 687, 1185, 2205,
3216, 3584, 3922, 4316,
5312, 7056, 7057, 7085,
7556, 7590 )
GROUP BY inv.CustomerID,
inv.WareHouseID,
inv.OperateNum,
inv.SKU,
inv.OriginBillNumber) pu
ON wk.WareHouseID = pu.WareHouseID
AND wk.SKU = pu.SKU) tep
WHERE tep.DifferenceNum >= 1
AND tep.DifferenceNum <= 9999999
3. 原sql http://pm.zhcxkj.com/zentao/bug-view-29163.html，已给解决方案，参考优化
SELECT COUNT(1) FROM (
selecttep.WareHouseID
--,rbwh.WareHouseName
--,tep.SKU
--,tep.PackageID
--,tep.OutPutNum
--,tep.OriginBillNumber
--,tep.OperateNum as SumOperateNum
--,tep.InWareHouseID
--,inwh.WareHouseName as InWareHouseName
--,tep.DifferenceNum
--,ilog.OperateNum
--,ilog.BillNumber
--,ilog.OperateTime
from
(
select
ISNULL(wk.CustomerID,pu.CustomerID) CustomerID,
ISNULL(wk.WareHouseID,pu.WareHouseID) WareHouseID,
pu.InWareHouseID,
ISNULL(wk.SKU,pu.SKU) SKU,
ISNULL(wk.PackageID,PU.PackageID) PackageID,
ISNULL(wk.OutPutNum,0) OutPutNum,
ISNULL(wk.AllocationNumber,pu.OriginBillNumber) OriginBillNumber,
ABS(ISNULL(pu.OperateNum,0)) OperateNum,
ABS(ISNULL(wk.OutPutNum,0)-ABS(ISNULL(pu.OperateNum,0))) as DifferenceNum
from
( select
put.CustomerID
,put.WareHouseID
,put.SKU
,ordl.PackageID
,bl.AllocationNumber
,SUM(put.OutPutNum) OutPutNum
from
RB_WareHouse_Product_OutPut_Log put with(NOLOCK)
left join RB_Procurement_Order_Package_OrderDetail ordl with(NOLOCK)
on put.CustomerID=ordl.CustomerID and put.OrderListID=ordl.OrderListID
left join RB_Inventory_AllocationDetailBill bill with(NOLOCK) on bill.CustomerID=ordl.CustomerID and bill.SKU=ordl.SKU and bill.OrderListID=ordl.OrderListID
left join RB_Inventory_AllocationBill bl with(NOLOCK) on bill.AllocationBillID=bl.ID
left join RB_OrderInfo ordi with(NOLOCK)
on put.CustomerID=ordi.CustomerID and put.WareHouseID=ordi.WareHouseID and put.OrderCode=ordi.OrderCode
where ordi.IsStockOrder=1 and bl.Status<>3 and bl.Status<>4
and put.CustomerID=1
and put.OutTime>='2022-03-28' and put.OutTime< DATEADD(DAY,1,'2022-04-28') and put.WareHouseID in (1100000,2,279,333,336,687,1185,2205,3216,3584,3922,4316,5312,7056,7057,7085,7556,7590) group by put.CustomerID,put.WareHouseID,put.SKU,ordl.PackageID,bl.AllocationNumber
) wk full join
( select
inv.CustomerID,
inv.WareHouseID,
bill.InWareHouseID,
inv.SKU,
pagd.PackageID,
inv.OriginBillNumber,
SUM(inv.OperateNum) OperateNum
from
RB_Inventory_DetailLog inv with(NOLOCK)
left join RB_Procurement_Order_Package_OrderDetail pagd with(NOLOCK) on inv.CustomerID=pagd.CustomerID and inv.OrderListID=pagd.OrderListID
left join RB_Inventory_AllocationBill bill with(NOLOCK) on inv.CustomerID=bill.CustomerID and inv.OriginBillNumber=bill.AllocationNumber and inv.WareHouseID=bill.OutWareHouseID
where inv.BillType=5 and inv.CustomerID=1 and inv.OperateTime>='2022-03-28' and inv.OperateTime< DATEADD(DAY,1,'2022-04-28') and inv.WareHouseID in (1100000,2,279,333,336,687,1185,2205,3216,3584,3922,4316,5312,7056,7057,7085,7556,7590) group by inv.CustomerID,inv.WareHouseID,bill.InWareHouseID,inv.SKU,pagd.PackageID,inv.OriginBillNumber
) pu on wk.WareHouseID=pu.WareHouseID and wk.SKU=pu.SKU and wk.PackageID=pu.PackageID and wk.AllocationNumber=pu.OriginBillNumber
) tep
left join (select * from RB_Inventory_DetailLog with(NOLOCK) where BillType=5 and OperateTime>='2022-03-28' and OperateTime< DATEADD(DAY,1,'2022-04-28') ) ilog
on ilog.CustomerID=tep.CustomerID and ilog.WareHouseID=tep.WareHouseID and ilog.SKU=tep.SKU and ilog.OriginBillNumber=tep.OriginBillNumber
left join RB_WareHouse rbwh with(NOLOCK) on tep.WareHouseID=rbwh.ID
left join RB_WareHouse inwh with(NOLOCK) on tep.InWareHouseID=inwh.ID
where rbwh.WareHouseType=1 and tep.DifferenceNum>=1 and tep.DifferenceNum<=9999999 ) AS PagerTable
--PARAMS:@CustomerID = 1 ,@StratTime = 2022-03-28 ,@EndTime = 2022-04-28 ,@StratNum = 1 ,@EndNum = 9999999
