[步骤]

---SKU供应商首次出现 -- 初始化变量 DECLARE @LastMaxID INT = 24310757; -- 上一次返回的ID DECLARE @MaxID INT; -- 设置@MaxID为最大的ID值 SELECT @MaxID = MAX(ID) FROM ( SELECT TOP 5000 pd.ID FROM RB_Procurement_Detail pd LEFT JOIN RB_Procurement_List pl 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表，设置IsFirstSupplierOrder为1 UPDATE pd1 SET pd1.IsFirstSupplierOrder = 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.IsFirstSupplierOrder = 0 AND pd1.ID > @LastMaxID AND pd1.ID <= @MaxID AND ( ( pd1.ChangeSupplierID > 0 AND pd1.ChangeSupplierID <> (SELECT COALESCE(( SELECT TOP 1 CASE WHEN pd2.ChangeSupplierID > 0 THEN pd2.ChangeSupplierID ELSE pl2.SupplierID END AS SupplierID FROM RB_Procurement_Detail pd2 INNER JOIN RB_Procurement_List pl2 ON pd2.CustomerID = pl2.CustomerID AND pd2.P_Code = pl2.P_Code WHERE pd1.CustomerID = pd2.CustomerID AND ISNULL(pl2.IsSample,0) = 0 AND pd1.sku = pd2.sku AND pd1.ID > pd2.ID ORDER BY pd2.ID DESC ), 0) AS SupplierID) ) OR ( pd1.ChangeSupplierID = 0 AND pl.SupplierID <> (SELECT COALESCE(( SELECT TOP 1 CASE WHEN pd2.ChangeSupplierID > 0 THEN pd2.ChangeSupplierID ELSE pl2.SupplierID END AS SupplierID FROM RB_Procurement_Detail pd2 INNER JOIN RB_Procurement_List pl2 ON pd2.CustomerID = pl2.CustomerID AND pd2.P_Code = pl2.P_Code WHERE pd1.CustomerID = pd2.CustomerID AND ISNULL(pl2.IsSample,0) = 0 AND pd1.sku = pd2.sku AND pd1.ID > pd2.ID ORDER BY pd2.ID DESC ), 0) AS SupplierID) ) ) AND EXISTS ( SELECT TOP 1 CASE WHEN pd2.ChangeSupplierID > 0 THEN pd2.ChangeSupplierID ELSE pl2.SupplierID END AS SupplierID FROM RB_Procurement_Detail pd2 INNER JOIN RB_Procurement_List pl2 ON pd2.CustomerID = pl2.CustomerID AND pd2.P_Code = pl2.P_Code WHERE pd1.CustomerID = pd2.CustomerID AND ISNULL(pl2.IsSample,0) = 0 AND pd1.sku = pd2.sku AND pd1.ID > pd2.ID ORDER BY pd2.ID DESC ); -- 更新RB_Procurement_List表，设置IsFirstSupplierOrder为1 UPDATE pl SET pl.IsFirstSupplierOrder = 1 FROM RB_Procurement_List pl WHERE pl.CustomerID = 1 AND pl.IsFirstSupplierOrder = 0 AND EXISTS ( SELECT 1 FROM RB_Procurement_Detail pd WHERE pd.CustomerID = pl.CustomerID AND pd.P_Code = pl.P_Code AND pd.IsFirstSupplierOrder = 1 AND pd.ID > @LastMaxID AND pd.ID <= @MaxID ); -- 返回@MaxID SELECT @MaxID AS MaxID;

[结果]

[期望]