尝试：
DECLARE @t TABLE ( ID INT , wac DECIMAL(30, 10) , item CHAr(1) )DECLARE @b TABLE ( item CHAr(1) , baseline DECIMAL(30, 10) )INSERT INTO @tVALUES ( 1, 2.31, 'A' ), ( 2, 1.10, 'A' ), ( 3, 2.13, 'A' ), ( 4, 1.34, 'A' )INSERT INTO @bVALUES ( 'A', 10 );WITH ordercte AS ( SELECt * , ROW_NUMBER() OVER ( PARTITION BY item ORDER BY ID ) AS rn FROM @t ), rec AS ( SELECt t.item , t.ID , t.wac , t.rn , b.baseline * ( 1 + ( t.wac / 100 ) ) AS m FROM ordercte t JOIN @b b ON b.item = t.item WHERe t.rn = 1 UNIOn ALL SELECt t.item , t.ID , t.wac , t.rn , c.m * ( 1 + ( t.wac / 100 ) ) FROM ordercte t JOIN rec c ON t.item = c.item AND t.rn = c.rn + 1 ) SELECt id , wac , item , m FROM rec
输出：
id wac item m1 2.3100000000 A 10.2310002 1.1000000000 A 10.3435413 2.1300000000 A 10.5638584 1.3400000000 A 10.705414
编辑1
我试图实现LOG EXP技巧，但除非@usr将我引向解决方案，否则无法进行管理。因此，所有功劳归于用户@usr：
WITH ordercte AS ( SELECt t.ID , t.wac , t.item , b.baseline , ROW_NUMBER() OVER ( PARTITION BY t.item ORDER BY ID ) AS rn FROM @t t JOIN @b b ON b.item = t.item ) SELECt baseline * EXP(SUM(LOG(( 1 + ( wac / 100 ) ))) OVER ( PARTITION BY item ORDER BY rn )) AS m FROM ordercte
要不就：
SELECt t.ID, t.wac, t.item, baseline * EXP(SUM(LOG(( 1 + ( wac / 100 ) ))) OVER ( PARTITION BY t.item ORDER BY t.ID )) AS mFROM @t t JOIN @b b ON b.item = t.item
如果ID是您订购的字段。
输出：
ID wac item m1 2.3100000000 A 10.2312 1.1000000000 A 10.3435413 2.1300000000 A 10.56385842334 1.3400000000 A 10.7054141261722
编辑2
对于SQL 2008，请使用：
WITH cte AS ( SELECt t.ID , t.wac , t.item , baseline , ( SELECT SUM(LOG(( 1 + ( wac / 100 ) ))) FROM @t it WHERe it.item = t.item AND it.ID <= t.ID ) AS e FROM @t t JOIN @b b ON b.item = t.item ) SELECt ID, wac, item, baseline * EXP(e) AS m FROM cte
编辑3
这是使用NULL和负值进行拨号的SQL Server 2008完整解决方案：
WITH cte AS ( SELECt t.ID , t.wac , t.item , b.baseline , ca.e, ca.n, ca.m FROM @t t JOIN @b b ON b.item = t.item CROSS APPLY(SELECt SUM(LOG(ABS(NULLIF( 1 + wac / 100 , 0)))) as e, SUM(SIGN(CASE WHEN 1 + wac / 100 < 0 THEN 1 ELSE 0 END)) AS n, MIN(ABS(1 + wac / 100)) AS m FROM @t it WHERe it.item = t.item AND it.ID <= t.ID ) ca ) SELECt ID, wac, item, baseline * CASE WHEN m = 0 THEN 0 WHEN n % 2 = 1 THEN -1 * EXP(e) ELSE EXP(e) END as Result FROM cte
