SQL Server 2022：信用额度内逆序采购记录查询实现

需求是显示客户在信用额度大于0时的所有采购记录（金额、日期、名称），按逆时间顺序排列，最后一行显示贷款余额。规则为：按采购时间从新到旧的顺序，用客户初始信用额度依次抵扣采购金额，剩余信用>0时输出完整采购记录；若抵扣后信用不足，则只输出能覆盖的金额，最后统一显示剩余贷款余额。

表结构与测试数据 #

DECLARE @Table1 table (Id_Client int, Value money) -- 客户表：Id_Client为客户ID，Value为初始信用额度

INSERT INTO @Table1 (Id_Client, Value)
SELECT 1, 24
UNION SELECT 2, 13
UNION SELECT 3, 2
UNION SELECT 4, 5

DECLARE @Table2 table (Id_Client int, DocDate datetime, Amount money, Caption varchar(6)) -- 采购记录表：Id_Client客户ID，Amount采购金额，DocDate采购日期，Caption采购名称

INSERT INTO @Table2 (Id_Client, Amount, DocDate, Caption)
SELECT 1, 5, '20051024', 'qh'
UNION SELECT 1, 9, '20051019', 'wj'
UNION SELECT 1, 3, '20051022', 'ek'
UNION SELECT 1, 8, '20051004', 'rl'
UNION SELECT 1, 6, '20051018', 'tz'
UNION SELECT 1, 5, '20050929', 'yx'
UNION SELECT 2, 11, '20051023', 'uc'
UNION SELECT 2, 6, '20051021', 'iv'
UNION SELECT 2, 45, '20051018', 'ob'
UNION SELECT 3, 4, '20051030', 'pn'
UNION SELECT 3, 2, '20051028', 'am'
UNION SELECT 4, 4, '20051021', 'sq'
UNION SELECT 4, 6, '20051023', 'dw'
UNION SELECT 4, 8, '20051023', 'fe'
UNION SELECT 4, 9, '20051023', 'gr'

预期结果 #

1 2005-10-24 00:00:00 5.00   qh
1 2005-10-22 00:00:00 3.00   ek
1 2005-10-19 00:00:00 9.00   wj
1 2005-10-18 00:00:00 6.00   tz
1 2005-10-04 00:00:00 1.00   rl
2 2005-10-23 00:00:00 11.00 uc
2 2005-10-21 00:00:00 2.00   iv
3 2005-10-30 00:00:00 2.00   pn
4 2005-10-23 00:00:00 5.00   gr

用户现有查询（未达到预期） #

用户使用SQL Server 2022，编写的查询仅筛选了有信用额度的客户采购记录，但未实现信用抵扣逻辑：

WITH AllPurchases AS (
    SELECT
        t2.Id_Client,
        t2.DocDate,
        t2.Amount,
        t2.Caption
    FROM
        Table2 t2
    WHERE
        t2.Id_Client IN (SELECT Id_Client FROM Table1 WHERE Value > 0)
)

SELECT 
    ap.Id_Client,
    ap.DocDate,
    ap.Amount,
    ap.Caption
FROM 
    AllPurchases ap
    INNER JOIN Table1 t1 ON ap.Id_Client = t1.Id_Client
WHERE 
    t1.Value > 0
ORDER BY 
    ap.Id_Client ASC, ap.DocDate DESC

解决方案 #

核心思路是按客户分组，将采购记录按逆时间排序，计算累计采购金额，和初始信用额度对比，判断哪些记录可以全额抵扣，哪些需要部分抵扣，最后追加剩余余额行：

WITH SortedPurchases AS (
    -- 按客户分组，采购记录逆时间排序，计算累计采购金额
    SELECT 
        t2.Id_Client,
        t2.DocDate,
        t2.Amount,
        t2.Caption,
        t1.Value AS InitialCredit,
        SUM(t2.Amount) OVER (PARTITION BY t2.Id_Client ORDER BY t2.DocDate DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeAmount
    FROM @Table2 t2
    JOIN @Table1 t1 ON t2.Id_Client = t1.Id_Client
    WHERE t1.Value > 0
),
ValidPurchases AS (
    -- 筛选有效采购记录：累计金额<=初始信用的全额显示，超出的部分显示剩余可抵扣金额
    SELECT 
        Id_Client,
        DocDate,
        CASE 
            WHEN CumulativeAmount <= InitialCredit THEN Amount
            ELSE InitialCredit - (CumulativeAmount - Amount)
        END AS Amount,
        Caption,
        InitialCredit - CumulativeAmount AS RemainingCredit
    FROM SortedPurchases
    WHERE CumulativeAmount - Amount < InitialCredit -- 确保当前记录至少能被部分抵扣
),
RemainingBalances AS (
    -- 计算每个客户的最终剩余余额
    SELECT 
        Id_Client,
        NULL AS DocDate,
        NULL AS Amount,
        '余额' AS Caption,
        MIN(RemainingCredit) AS RemainingCredit
    FROM ValidPurchases
    GROUP BY Id_Client
)
-- 合并采购记录和余额行，按客户+日期排序（余额行放在最后）
SELECT 
    Id_Client,
    DocDate,
    Amount,
    Caption
FROM ValidPurchases
UNION ALL
SELECT 
    Id_Client,
    DocDate,
    RemainingCredit AS Amount,
    Caption
FROM RemainingBalances
ORDER BY 
    Id_Client ASC, 
    CASE WHEN DocDate IS NULL THEN 1 ELSE 0 END ASC, -- 余额行排最后
    DocDate DESC;

说明 #

1. SortedPurchases：按客户分组，采购记录从新到旧排序，计算累计采购金额，方便和初始信用对比。
2. ValidPurchases：判断每条记录是否能全额抵扣，若累计金额超过初始信用，只显示剩余可抵扣的部分。
3. RemainingBalances：计算每个客户抵扣后的最终余额。
4. 最后合并结果，确保余额行在每个客户的采购记录之后。

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