DECLARE @nmSupport NUMERIC(14,4),@nmOutst NUMERIC(14,4),@chPart VARCHAR(16) SELECT @chPart = 'XXX' SELECT @nmOutst = 0 SELECT @nmSupport = 0 UPDATE acdb.PAC06.AC06004A SET nmSupport = CASE WHEN chPart = @chPart THEN CASE WHEN nmQty > @nmOutst THEN @nmOutst ELSE nmQty END ELSE CASE WHEN nmQty > nmOutst THEN nmOutst ELSE nmQty END END, nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-nmSupport END, @nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-nmSupport END, @nmSupport = case when chPart = @chPart then case when nmQty > @nmOutst then @nmOutst else nmQty end ELSE CASE WHEN nmQty > nmOutst THEN nmOutst ELSE nmQty END end, @chPart = chPart改成用SQLSERVE游标遍历的方式

可以使用游标遍历实现相同的功能，以下是使用游标的代码示例：

DECLARE @nmSupport NUMERIC(14,4),@nmOutst NUMERIC(14,4),@chPart VARCHAR(16)

DECLARE c CURSOR FOR
SELECT chPart, nmQty, nmOutst, nmSupport
FROM acdb.PAC06.AC06004A

OPEN c

FETCH NEXT FROM c INTO @chPart, @nmQty, @nmOutst, @nmSupport

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @chPart = 'XXX'
    BEGIN
        SET @nmSupport = CASE WHEN @nmQty > @nmOutst THEN @nmOutst ELSE @nmQty END
    END
    ELSE
    BEGIN
        SET @nmSupport = CASE WHEN @nmQty > @nmOutst THEN @nmOutst ELSE @nmQty END
    END

    UPDATE acdb.PAC06.AC06004A
    SET nmSupport = @nmSupport,
        nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-@nmSupport END,
        @nmOutst = CASE WHEN chPart = @chPart then case when @nmOutst>=@nmSupport then @nmOutst-@nmSupport else 0 end else nmOutst-@nmSupport END
    WHERE CURRENT OF c

    FETCH NEXT FROM c INTO @chPart, @nmQty, @nmOutst, @nmSupport
END

CLOSE c
DEALLOCATE c

请注意，游标可能会对性能产生影响，因此应该谨慎使用。