SQL Server存储过程开发：入账金额冲销债务查询实现需求

需求背景 #

需要开发存储过程，实现入账金额冲销现有债务的逻辑：入账金额按顺序抵扣表中Amount_Rest，抵扣完成的行Amount置0，剩余未完全抵扣的行更新Amount_Rest为剩余值。

当前表数据 #

Date            Amount  Amount_Rest
2018-12-05  150,00  150,00
2018-12-05  150,00  150,00
2018-12-08  100,00  100,00
2018-12-08  100,00  100,00
2018-12-11  170,00  170,00
2018-12-11  170,00  170,00
2018-12-14  105,00  105,00
2018-12-14  105,00  105,00
2018-12-16  45,00   45,00

冲销预期结果（入账金额250.00） #

Date            Amount  Amount_Rest
2018-12-05    0,00    0,00
2018-12-05    0,00   50,00
2018-12-08  100,00  100,00
2018-12-08  100,00  100,00
2018-12-11  170,00  170,00
2018-12-11  170,00  170,00
2018-12-14  105,00  105,00
2018-12-14  105,00  105,00
2018-12-16  45,00   45,00

已尝试的游标代码 #

DECLARE 
     @D_Date    date 
    ,@N_amount  money

DECLARE 
    cursor_pay CURSOR 
FOR SELECT
     D_Date
    ,N_Amount
FROM 
    dbo.FD_Bills

OPEN cursor_pay

WHILE @@FETCH_STATUS = 0
    BEGIN 
        PRINT
            CAST(@D_Date AS varchar) +
            CAST(@N_amount AS varchar);
        FETCH NEXT FROM cursor_pay INTO
             @D_Date
            ,@N_amount
    END;

CLOSE cursor_pay;
DEALLOCATE cursor_pay;

推荐用基于集合的操作替代游标，效率更高且更符合SQL设计理念。以下是实现冲销逻辑的存储过程：

CREATE PROCEDURE dbo.sp_ProcessDebtWriteOff
    @IncomingAmount MONEY
AS
BEGIN
    SET NOCOUNT ON;

    -- 为账单按顺序编号，计算累计剩余债务
    WITH NumberedBills AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY [Date]) AS BillSeq,
            SUM(Amount_Rest) OVER (ORDER BY [Date] ROWS UNBOUNDED PRECEDING) AS TotalCumulativeRest
        FROM dbo.FD_Bills
    ),
    CalculatedWriteOff AS (
        SELECT 
            BillSeq,
            -- 计算剩余未冲销金额
            CASE
                WHEN @IncomingAmount <= 0 THEN Amount_Rest
                WHEN TotalCumulativeRest - Amount_Rest >= @IncomingAmount THEN Amount_Rest
                WHEN TotalCumulativeRest <= @IncomingAmount THEN 0
                ELSE Amount_Rest - (@IncomingAmount - (TotalCumulativeRest - Amount_Rest))
            END AS New_Amount_Rest,
            -- 按预期更新Amount字段：有冲销则置0，否则保留原值
            CASE
                WHEN @IncomingAmount <= 0 THEN Amount
                WHEN TotalCumulativeRest - Amount_Rest < @IncomingAmount THEN 0
                ELSE Amount
            END AS New_Amount
        FROM NumberedBills
    )
    -- 更新原表数据
    UPDATE b
    SET 
        Amount = c.New_Amount,
        Amount_Rest = c.New_Amount_Rest
    FROM dbo.FD_Bills b
    JOIN NumberedBills nb ON b.[Date] = nb.[Date] 
        AND b.Amount = nb.Amount 
        AND b.Amount_Rest = nb.Amount_Rest
    JOIN CalculatedWriteOff c ON nb.BillSeq = c.BillSeq;
END
GO

使用示例 #

-- 传入入账金额250.00执行冲销
EXEC dbo.sp_ProcessDebtWriteOff @IncomingAmount = 250.00;

关键说明 #

• 冲销顺序：默认按Date升序冲销，若需调整优先级，修改ROW_NUMBER() OVER (ORDER BY [Date])中的排序字段即可（比如加入债务优先级字段）。
• 重复行处理：示例中通过Date+Amount+Amount_Rest匹配行，若表有主键（如BillID），建议用主键关联，避免匹配错误。
• 边界处理：已覆盖入账金额为0、大于总债务、部分冲销等场景。

内容的提问来源于stack exchange，提问作者quaka