基于分区列的递归CTE实现各TID独立累计余额的方案咨询

Got it, let's tweak your recursive CTE to calculate running totals per TID instead of a global one. The changes are minimal but crucial—here's how to do it:

First, we need to ensure each TID gets its own sequence of row numbers, then make sure the recursion only links rows within the same TID so the running total doesn't bleed between groups.

Modified Full Code #

drop table if exists #Transactions 
create table #Transactions (TID int, amt int) 
-- TID 1 transactions
insert into #Transactions values(1, 100) 
insert into #Transactions values(1, -50) 
insert into #Transactions values(1, 100) 
insert into #Transactions values(1, -100) 
insert into #Transactions values(1, 200) 
-- TID 2 transactions
insert into #Transactions values(2, 100) 
insert into #Transactions values(2, -50) 
insert into #Transactions values(2, 100) 
insert into #Transactions values(2, -100) 
insert into #Transactions values(2, 200)

;WITH y AS ( 
 SELECT 
     TID, 
     amt, 
     -- Partition row numbers by TID to get per-group sequences
     rn = ROW_NUMBER() OVER (PARTITION BY TID ORDER BY (SELECT NULL)) 
 FROM #Transactions 
), x AS ( 
 -- Anchor: Grab the first row of each TID with initial running total
 SELECT TID, rn, amt, rt = amt 
 FROM y WHERE rn = 1 
 UNION ALL 
 -- Recursive: Only join to the next row in the SAME TID
 SELECT y.TID, y.rn, y.amt, x.rt + y.amt 
 FROM x INNER JOIN y ON y.TID = x.TID AND y.rn = x.rn + 1 
) 
SELECT TID, amt, RunningTotal = rt FROM x ORDER BY TID, rn OPTION (MAXRECURSION 10000);

Key Changes Explained #

• Partitioned Row Numbers: In the y CTE, adding PARTITION BY TID to ROW_NUMBER() ensures each TID starts its row numbering at 1, instead of a global sequence across all transactions.
• TID-Locked Recursion: The recursive join now includes y.TID = x.TID, so we only build the running total within the same TID group—no carryover from TID 1 to TID 2.
• Clean Output Order: The final ORDER BY TID, rn groups results by TID and displays them in transaction sequence.

A quick note: The ORDER BY (SELECT NULL) is a placeholder if you don't have an explicit transaction order column (like a timestamp). If your table has a column that defines the order of transactions (e.g., TransDate), replace that with the actual column to guarantee consistent running totals every time.

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