[步骤]
DECLARE @NodeStatusList2 INT= 2,@OrderNoList VARCHAR(50)= '2809318155727221774',
@OrderStatusList INT= 2,
@DepartmentIdList VARCHAR(200)= '59,60,112,585,586,732,65,66,161,189,193,349,405,563,584,624,191',
@NodeDealAdminIDs VARCHAR(20)= '9795'
SELECT Count(1)
FROM RB_ProcurementAbnormal_OrderInfo paoi WITH ( NOLOCK )
LEFT JOIN RB_Product(NOLOCK) p
ON p.CustomerID = paoi.CustomerID
AND p.SKU = paoi.SKU
LEFT JOIN RB_WareHouse_Position(NOLOCK) wp
ON wp.CustomerID = paoi.CustomerID
AND wp.ID = paoi.AbnormalPositionID
LEFT JOIN RB_Procurement_Detail_SingleList pds(NOLOCK)
ON paoi.SingleNo = pds.Single_NO
AND paoi.CustomerID = pds.CustomerID
LEFT JOIN RB_Procurement_Shipping_Insurance psi(NOLOCK)
ON psi.Foreignkey = pds.ID
LEFT JOIN RB_Procurement_List pl
ON paoi.P_Code = pl.P_Code
AND paoi.CustomerID = pl.CustomerID
LEFT JOIN RB_ProcurementAbnormal_AbnormalReasonSetting paar(NOLOCK)
ON paar.ID = paoi.AbnormalReasonID
AND paar.CustomerID = paoi.CustomerID
LEFT JOIN RB_Product_Supplier(NOLOCK) ps
ON ps.CustomerID = paoi.CustomerID
AND ps.ID = paoi.SupplierID
WHERE paoi.CustomerID = 1
AND paoi.OrderCode IN (SELECT DISTINCT OrderCode
FROM RB_ProcurementAbnormal_OrderInfo_RelationNode WITH(nolock)
WHERE Id = paoi.NodeInProcessing
AND NodeStatus IN (SELECT Cast(item AS INT)
FROM Charsplitforin(@NodeStatusList2, ',')))
AND NOT EXISTS(SELECT 1
FROM RB_ProcurementAbnormal_AuditRecord(NOLOCK)
WHERE OrderCode = paoi.OrderCode
AND CustomerID = paoi.CustomerID
AND AuditStatus = 1)
AND paoi.SingleNo IN (SELECT DISTINCT Single_NO
FROM RB_Procurement_Detail_SingleList WITH(nolock)
WHERE OrderNo IN (SELECT item
FROM Charsplitforin(@OrderNoList, ',')))
AND paoi.OrderStatus IN (SELECT Cast(item AS INT)
FROM Charsplitforin(@OrderStatusList, ','))
AND paoi.OrderCode IN (SELECT DISTINCT OrderCode
FROM RB_ProcurementAbnormal_OrderInfo_RelationNode WITH(nolock)
WHERE Id = paoi.NodeInProcessing
AND PendingDepartmentId IN (SELECT Cast(item AS INT)
FROM Charsplitforin(@DepartmentIdList, ',')))
AND paoi.OrderCode IN (SELECT DISTINCT OrderCode
FROM RB_ProcurementAbnormal_OrderInfo_RelationNode WITH(nolock)
WHERE Id = paoi.NodeInProcessing
AND DealAdminID IN (SELECT Cast(item AS INT)
FROM Charsplitforin(@NodeDealAdminIDs, ',')))
缺少 nolock
[结果]
[期望]
LEFT JOIN RB_Procurement_List pl 补全 nolock ，其他如果没有发现的也一概补全
