凭证账龄冲销金额计算：SQL查询结果调整需求

现有两张表：Payment表和Deduction表，当前SQL查询返回结果如下，但需要获取实际已抵扣的金额，调整后得到期望结果。

当前查询结果 #

期望结果 #

当前使用的SQL代码 #

DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);

INSERT @Deductions (DeductionAmount) VALUES (1000),(150);

DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);

INSERT @Payments (PaymentAmount) VALUES (500),(550),(100);

-- Preprocessing

IF OBJECT_ID('tempdb..#Deductions') IS NOT NULL DROP TABLE #Deductions;

SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]
INTO #Deductions
FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d;

CREATE UNIQUE CLUSTERED INDEX ucx_DeductionID ON #Deductions (DeductionID);

IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments;

SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]
INTO #Payments
FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d;

CREATE UNIQUE CLUSTERED INDEX ucx_PaymentID ON #Payments (PaymentID);

-- Generate result set
-- Note that Deduction 2 is covered by Payments 3 AND 4.
-- Please check your figures in your expected result set

SELECT
    DeductionID, 
    DeductionAmount, --d.[from], d.[to],
    PaymentID, 
    PaymentAmount, --p.[from], p.[to],
    DeductionBalance = CASE WHEN d.[to] > p.[to] THEN 
                        d.[to] - p.[to] 
                    ELSE 
                        0 
                    END,
    PaymentBalance = CASE WHEN p.[to] > d.[to] THEN 
                    p.[to] - d.[to] 
                WHEN d.[to] IS NULL THEN 
                    PaymentAmount 
                ELSE 0 END
FROM #Deductions d
FULL OUTER JOIN #Payments p
    ON p.[from] < d.[to] AND p.[to] > d.[from]
ORDER BY ISNULL(d.DeductionID, 1000000), p.PaymentID;

调整后的SQL代码 #

核心修改是重新计算DeductionBalance和PaymentBalance字段：

• 先计算每一对抵扣与支付的实际抵扣金额
• PaymentBalance直接取该实际抵扣金额
• DeductionBalance用抵扣项总金额减去该抵扣项累计已抵扣的金额

DECLARE @Deductions TABLE (DeductionID int IDENTITY(1,1),DeductionAmount money);

INSERT @Deductions (DeductionAmount) VALUES (1000),(150);

DECLARE @Payments TABLE (PaymentID int IDENTITY(1,1),PaymentAmount money);

INSERT @Payments (PaymentAmount) VALUES (500),(550),(100);

-- Preprocessing

IF OBJECT_ID('tempdb..#Deductions') IS NOT NULL DROP TABLE #Deductions;

SELECT DeductionID, DeductionAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY DeductionID),0), [to]
INTO #Deductions
FROM (SELECT *, [to] = SUM(DeductionAmount) OVER (ORDER BY DeductionID) FROM @Deductions) d;

CREATE UNIQUE CLUSTERED INDEX ucx_DeductionID ON #Deductions (DeductionID);

IF OBJECT_ID('tempdb..#Payments') IS NOT NULL DROP TABLE #Payments;

SELECT PaymentID, PaymentAmount, [from] = ISNULL(LAG([to],1) OVER (ORDER BY PaymentID),0), [to]
INTO #Payments
FROM (SELECT *, [to] = SUM(PaymentAmount) OVER (ORDER BY PaymentID) FROM @Payments) d;

CREATE UNIQUE CLUSTERED INDEX ucx_PaymentID ON #Payments (PaymentID);

-- Generate result set with actual deducted amounts
SELECT
    DeductionID, 
    DeductionAmount,
    PaymentID, 
    PaymentAmount,
    -- 计算当前匹配对的实际抵扣金额
    used_amount = LEAST(d.[to], p.[to]) - GREATEST(d.[from], p.[from]),
    -- 抵扣项剩余金额 = 总金额 - 累计已抵扣金额
    DeductionBalance = DeductionAmount - SUM(LEAST(d.[to], p.[to]) - GREATEST(d.[from], p.[from])) OVER (PARTITION BY d.DeductionID ORDER BY p.PaymentID),
    -- 支付单用于当前抵扣项的金额 = 实际抵扣金额
    PaymentBalance = LEAST(d.[to], p.[to]) - GREATEST(d.[from], p.[from])
FROM #Deductions d
FULL OUTER JOIN #Payments p
    ON p.[from] < d.[to] AND p.[to] > d.[from]
ORDER BY ISNULL(d.DeductionID, 1000000), p.PaymentID;

说明 #

• LEAST(d.[to], p.[to]) - GREATEST(d.[from], p.[from])：精准计算抵扣与支付的重叠部分，即实际用于抵扣的金额
• 窗口函数SUM(...) OVER (PARTITION BY d.DeductionID ORDER BY p.PaymentID)：按抵扣项分组，累计计算已抵扣的金额，用总抵扣金额减去累计值得到剩余余额
• PaymentBalance直接返回当前匹配的实际抵扣金额，符合期望结果中“实际已抵扣金额”的需求

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