在没有完全运行[fMath]。[mfCalc_RunningTotalBookCostPnL]正常运行的情况下，这有点暗中的刺伤。我在测试之前就第一次获得递归CTE的经验大约只有50％，但即使不完美，只要我正确理解了您的要求，它也足以让您入门：
-- First, cache Table1 into #temp to improve recursive CTE performanceselectRowNum=ROW_NUMBER()OVER(ORDER BY OrderID), *INTO #tempFROM Table1;GO; WITH CTE (RowNum,OrderID, BuySell, FilledSize, ExecutionPrice, RunningTotal, AverageBookCost, RealisedPnL) AS ( SELECt RowNum,OrderID, BuySell, FilledSize, ExecutionPrice, RunningTotal=0, AverageBookCost=0, RealisedPnL=0 FROM #temp WHERe RowNum=1 UNIOn ALL SELECt t.RowNum, t.OrderID, t.BuySell, t.FilledSize, t.ExecutionPrice , RunningTotal=c.NewRunningTotal, AverageBookCost=c.NewBookCost, RealisedPnL=c.PreMultRealisedPnL FROM #temp t INNER JOIN CTE ON CTE.RowNum+1 = t.RowNum CROSS APPLY [fMath].[mfCalc_RunningTotalBookCostPnL](t.BuySell, t.FilledSize, t.ExecutionPrice, CTE.RunningTotal, CTE.AverageBookCost) AS c)SELECt OrderID, BuySell, FilledSize, ExecutionPrice, RunningTotal, AverageBookCost, RealisedPnLFROM CTEOPTION (MAXRECURSION 32767);GO-- clean upDROP TABLE #tempGO
另一个免责声明-递归CTE的最大深度为32767是很好的。如果限制太严格，则需要探索另一种方法或对数据集进行某种形式的窗口化。
