[步骤]
----SKU首次出现 -- 初始化变量
DECLARE @LastMaxID INT = 24612444; -- 上一次返回的ID
DECLARE @MaxID INT; -- 设置@MaxID为最大的ID值
SELECT @MaxID = Max(ID)
FROM (SELECT TOP 5000 pd.ID
FROM RB_Procurement_Detail pd (NOLOCK)
LEFT JOIN RB_Procurement_List pl (NOLOCK)
ON pd.CustomerID = pl.CustomerID
AND pd.P_Code = pl.P_Code
WHERE Isnull(pl.IsSample, 0) = 0
AND pd.ID > @LastMaxID
ORDER BY pd.ID ASC) AS subquery;
-- 更新RB_Procurement_Detail表，设置IsFirstOrder为1
UPDATE pd1
SET pd1.IsFirstOrder = 1
FROM RB_Procurement_Detail pd1
LEFT JOIN RB_Procurement_List pl
ON pd1.CustomerID = pl.CustomerID
AND pd1.P_Code = pl.P_Code
WHERE pd1.CustomerID = 1
AND Isnull(pl.IsSample, 0) = 0
AND pd1.IsFirstOrder = 0
AND pd1.ID > @LastMaxID
AND pd1.ID <= @MaxID
AND NOT EXISTS (SELECT 1
FROM RB_Procurement_Detail pd2
LEFT JOIN RB_Procurement_List pl2
ON pd2.CustomerID = pl2.CustomerID
AND pd2.P_Code = pl2.P_Code
WHERE pd2.CustomerID = 1
AND Isnull(pl2.IsSample, 0) = 0
AND pd1.sku = pd2.sku
AND pd2.ID < pd1.ID);
-- 更新RB_Procurement_List表，设置IsFirstOrder为1
UPDATE pl
SET pl.IsFirstOrder = 1
FROM RB_Procurement_List pl
WHERE pl.CustomerID = 1
AND pl.IsFirstOrder = 0
AND EXISTS (SELECT 1
FROM RB_Procurement_Detail pd
WHERE pd.CustomerID = pl.CustomerID
AND pd.P_Code = pl.P_Code
AND pd.IsFirstOrder = 1
AND pd.ID > @LastMaxID
AND pd.ID <= @MaxID); -- 返回@MaxID
SELECT @MaxID AS MaxID;
[结果]
[期望]
优化SQL 提升效率 减少阻塞