递归CTE返回重复行：SQL Server交易滚动余额计算去重求助

我在SQL Server中有两张表：Transactions和TransactionDetails，表结构如下：

Transactions表列信息 #

TransactionId
Amount
ProcessedDate
TransactionDetailsId

TransactionDetails表列信息 #

TransactionDetailsId
AdvanceId

我已设定ClearedBalance和PendingBalance的初始值，需要编写查询从指定交易（TransactionId=8743）开始，选取所有ProcessedDate晚于该交易的记录，并计算每笔交易的滚动余额。

我尝试用递归CTE实现逻辑，但返回结果包含重复行，且重复行数呈指数级增长。我的代码如下：

-- Here we get current balances for advance
DECLARE @InitialPendingBalance  DECIMAL(18, 2) = 1000000;
DECLARE @InitialClearedBalance DECIMAL(18, 2) = 1000000;

WITH RecursiveBalances AS 
(
    -- Base case: Start with the new transaction
    SELECT
        t.TransactionId,
        td.AdvanceId,
        t.Amount,
        CAST(@InitialPendingBalance + t.Amount AS DECIMAL(18, 2)) AS PendingBalance,
        CAST(@InitialClearedBalance + t.Amount AS DECIMAL(18, 2)) AS ClearedBalance,
        t.ProcessedDate
    FROM 
        [transaction].Transactions t
    JOIN
        [transaction].TransactionDetails td ON t.TransactionDetailsId = td.TransactionDetailId 
    WHERE 
        t.TransactionId = 8743

    UNION ALL

    -- Recursion part
    SELECT
        t.TransactionId,
        td.AdvanceId,
        t.Amount,
        CAST(rb.PendingBalance + t.Amount AS DECIMAL(18, 2)) AS PendingBalance,
        CAST(rb.ClearedBalance + t.Amount AS DECIMAL(18, 2)) AS ClearedBalance,
        t.ProcessedDate
    FROM
        [transaction].Transactions t
    JOIN
        [transaction].TransactionDetails td ON t.TransactionDetailsId = td.TransactionDetailId 
                                            AND td.AdvanceId = 100000800
    JOIN 
        RecursiveBalances rb ON t.ProcessedDate > rb.ProcessedDate
)
SELECT
    TransactionId, 
    COUNT(*) 
FROM
    RecursiveBalances 
GROUP BY
    TransactionId 
ORDER BY
    COUNT(*);

执行分组统计查询：

select 
    TransactionId, 
    count(*) 
from RecursiveBalances 
group by TransactionId 
order by count(*)

得到结果：

请问该如何解决重复行问题？

问题根源 #

递归CTE出现重复行的核心原因是：递归部分的关联条件t.ProcessedDate > rb.ProcessedDate会让每一条已生成的递归记录都和所有符合日期条件的新交易关联，导致同一笔交易被多次计算，最终出现指数级重复。

优先方案：用窗口函数替代递归CTE #

窗口函数SUM() OVER()可以更简单高效地计算滚动余额，且完全避免重复行问题。完整代码如下：

DECLARE @InitialPendingBalance  DECIMAL(18, 2) = 1000000;
DECLARE @InitialClearedBalance DECIMAL(18, 2) = 1000000;

-- 获取起始交易的ProcessedDate，用于筛选后续交易
DECLARE @StartProcessedDate DATETIME;
SELECT @StartProcessedDate = ProcessedDate
FROM [transaction].Transactions
WHERE TransactionId = 8743;

WITH FilteredTransactions AS (
    SELECT
        t.TransactionId,
        td.AdvanceId,
        t.Amount,
        t.ProcessedDate
    FROM [transaction].Transactions t
    JOIN [transaction].TransactionDetails td 
        ON t.TransactionDetailsId = td.TransactionDetailId
    WHERE 
        -- 包含起始交易，以及所有ProcessedDate晚于它的交易
        (t.TransactionId = 8743 OR t.ProcessedDate > @StartProcessedDate)
        -- 保留原递归中的AdvanceId过滤条件
        AND td.AdvanceId = 100000800
),
OrderedTransactions AS (
    SELECT
        *,
        -- 按交易时间+ID排序，累计计算金额
        SUM(Amount) OVER(ORDER BY ProcessedDate, TransactionId) AS TotalAmount
    FROM FilteredTransactions
)
SELECT
    TransactionId,
    AdvanceId,
    Amount,
    -- 初始余额加上累计金额得到滚动余额
    CAST(@InitialPendingBalance + TotalAmount AS DECIMAL(18,2)) AS PendingBalance,
    CAST(@InitialClearedBalance + TotalAmount AS DECIMAL(18,2)) AS ClearedBalance,
    ProcessedDate
FROM OrderedTransactions
ORDER BY ProcessedDate, TransactionId;

备选方案：修复递归CTE #

如果必须使用递归CTE，需要确保每次递归只关联下一条交易，而非所有日期更大的交易。可以通过ROW_NUMBER()给交易排序，再按序号关联：

DECLARE @InitialPendingBalance  DECIMAL(18, 2) = 1000000;
DECLARE @InitialClearedBalance DECIMAL(18, 2) = 1000000;

WITH FilteredTransactions AS (
    SELECT
        t.TransactionId,
        td.AdvanceId,
        t.Amount,
        t.ProcessedDate,
        -- 按交易时间+ID排序，给每条交易分配唯一序号
        ROW_NUMBER() OVER(ORDER BY t.ProcessedDate, t.TransactionId) AS RowNum
    FROM [transaction].Transactions t
    JOIN [transaction].TransactionDetails td 
        ON t.TransactionDetailsId = td.TransactionDetailId
    WHERE 
        (t.TransactionId = 8743 OR t.ProcessedDate > (SELECT ProcessedDate FROM [transaction].Transactions WHERE TransactionId=8743))
        AND td.AdvanceId = 100000800
),
RecursiveBalances AS (
    -- 基例：选取起始交易
    SELECT
        TransactionId,
        AdvanceId,
        Amount,
        CAST(@InitialPendingBalance + Amount AS DECIMAL(18,2)) AS PendingBalance,
        CAST(@InitialClearedBalance + Amount AS DECIMAL(18,2)) AS ClearedBalance,
        ProcessedDate,
        RowNum
    FROM FilteredTransactions
    WHERE TransactionId = 8743

    UNION ALL

    -- 递归：只关联序号+1的下一条交易
    SELECT
        ft.TransactionId,
        ft.AdvanceId,
        ft.Amount,
        CAST(rb.PendingBalance + ft.Amount AS DECIMAL(18,2)) AS PendingBalance,
        CAST(rb.ClearedBalance + ft.Amount AS DECIMAL(18,2)) AS ClearedBalance,
        ft.ProcessedDate,
        ft.RowNum
    FROM FilteredTransactions ft
    JOIN RecursiveBalances rb ON ft.RowNum = rb.RowNum + 1
)
SELECT
    TransactionId,
    AdvanceId,
    Amount,
    PendingBalance,
    ClearedBalance,
    ProcessedDate
FROM RecursiveBalances
ORDER BY RowNum;

方案说明 #

• 窗口函数方案逻辑清晰，性能远优于递归CTE（尤其是数据量较大时），是推荐的首选方案。
• 修复后的递归CTE通过序号关联，确保每次递归只生成一条新记录，彻底避免了重复行问题。

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