使用CTE筛选匹配数量汇总的SQL Server交易行问题

问题说明 #

• 需求：编写SQL Server的T-SQL查询，针对指定itemid和正整数quantity summary，按交易日期倒序筛选交易行，直到累计数量达到或超过目标值，需包含所有参与累计的行（包括刚好达标或剩余的行）。例如筛选item1的10数量时，需选中1+3+0+10对应的交易行后停止。
• 异常情况：现有CTE实现处理item#2时出错——本该只选中trid7、8（22+11=33刚好达标），但脚本多选了trid10、11，因后续负数行导致累计总和下降，触发了错误的筛选条件。

测试数据 #

create table #items(itemid int, qtySum money)

insert into #items 
values (1, 10), (2, 33), (3, 15), (4, 3),
       (5, 3), (6, 15), (7, 15)

create table #transactions
(
    itemid int, 
    trid int, 
    trdate date, 
    qty money
)

insert into #transactions 
values (1, 1, '20240212', 1),
       (1, 2, '20240211', 3),
       (1, 3, '20240211', 0),
       (1, 4, '20240210', 10),
       (1, 5, '20240209', 10),
       (1, 6, '20240208', 1),
       (2, 7, '20240212', 22),
       (2, 8, '20240211', 11),
       (2, 9, '20240210', -5),
       (2, 10, '20240210', -4),
       (2, 11, '20240209', 9),
       (2, 12, '20240209', 4),
       (2, 13, '20240209', 6),
       (3, 15, '20240212', 10),
       (3, 16, '20240211', -2),
       (3, 17, '20240210', -3),
       (3, 18, '20240209', 8),
       (3, 19, '20240208', 4),
       (3, 20, '20240207', 2),
       (4, 21, '20240212', 5),
       (5, 22, '20240212', 3),
       (5, 23, '20240211', 3),
       (5, 24, '20240210', 3),
       (7, 25, '20240212', 0)

原有错误代码 #

;WITH cte AS
( 
    SELECT 
        i.itemid, i.qtySum AS iQuantity,
        tr.trid, tr.qty AS trQuantity,
        SUM(tr.qty) OVER (PARTITION BY i.itemid ORDER BY tr.trdate desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_amount
    FROM 
        #items i 
    INNER JOIN
        #transactions tr ON i.itemid = tr.itemid
)
SELECT *
FROM cte
WHERE sum_amount - iQuantity < trQuantity 

UNION

SELECT i.itemid, i.qtySum, NULL, NULL, NULL 
FROM #items i 
WHERE NOT EXISTS (SELECT TOP 1 1 FROM cte c WHERE c.itemid = i.itemid)

DROP TABLE #items
DROP TABLE #transactions

解决方案代码 #

;WITH ranked_trans AS (
    SELECT 
        i.itemid,
        i.qtySum AS target_qty,
        tr.trid,
        tr.trdate,
        tr.qty,
        -- 按日期倒序、trid倒序生成唯一行号，避免日期相同导致排序歧义
        ROW_NUMBER() OVER (PARTITION BY i.itemid ORDER BY tr.trdate DESC, tr.trid DESC) AS rn,
        -- 计算到当前行的累计总和（按倒序行累加）
        SUM(tr.qty) OVER (PARTITION BY i.itemid ORDER BY tr.trdate DESC, tr.trid DESC ROWS UNBOUNDED PRECEDING) AS running_total
    FROM #items i
    LEFT JOIN #transactions tr ON i.itemid = tr.itemid
),
target_rows AS (
    SELECT 
        itemid,
        -- 找到第一个累计总和达标或超标的行号；若所有行累计都不达标，取所有交易行
        COALESCE(MIN(CASE WHEN running_total >= target_qty THEN rn END), MAX(rn)) AS max_rn
    FROM ranked_trans
    GROUP BY itemid
)
SELECT 
    rt.itemid,
    rt.target_qty,
    rt.trid,
    rt.qty,
    rt.running_total
FROM ranked_trans rt
JOIN target_rows tr ON rt.itemid = tr.itemid
WHERE rt.rn <= tr.max_rn
-- 补充无交易记录的item
UNION ALL
SELECT 
    i.itemid,
    i.qtySum,
    NULL,
    NULL,
    NULL
FROM #items i
WHERE NOT EXISTS (SELECT 1 FROM #transactions tr WHERE tr.itemid = i.itemid)
ORDER BY itemid, rn;

DROP TABLE #items;
DROP TABLE #transactions;

逻辑说明 #

1. ranked_trans CTE：给每个item的交易按日期倒序、trid倒序生成唯一行号，同时计算到当前行的累计总和，确保排序无歧义。
2. target_rows CTE：定位每个item中第一个累计总和达到或超过目标值的行号；若所有交易的累计总和都未达标，则取所有交易行的最大行号。
3. 最终查询：选中所有行号<=目标行号的交易，同时补充没有交易记录的item，确保结果完整。

测试结果验证 #

• item2：仅选中trid7、8（累计33刚好达标），不再多选后续负数行，解决了原异常。
• item1：选中trid1、2、3、4（累计14≥10），符合需求。
• 其他item均按规则筛选出正确的交易行。

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