递归公用表表达式 [英] Recursive Common Table Expression

问题描述

我知道我可能会采用错误的方式，但是我试图理解递归CTE。

I know I am probably going about this the wrong way, but I am trying to understand Recursive CTE's.

我创建了一个简单的表

RowNum  Type    Amount
1       Anch    10
2       Amt     1
3       Amt     2
4       Amt     3
5       Amt     4

这个想法是将金额固定为10，然后递归循环并从总数中删除金额。

The idea was to anchor at the amount 10, the to recursively loop through and remove the amount from the total.

我想出了以下内容

WITH cte_Rec (RowNum, [Type], Amount, Amount2, RT, RN)
     AS (SELECT RowNum,
                [Type],
                Amount,
                Amount,
                Amount,
                RowNum
         FROM   dbo.tbl_RecursiveCTE
         WHERE  [Type] = 'Anch'
         UNION ALL
         SELECT r.RowNum,
                r.[Type],
                r.Amount,
                ct.Amount,
                ct.Amount - r.Amount AS RT,
                ct.RowNum
         FROM   dbo.tbl_RecursiveCTE r
                INNER JOIN cte_Rec ct
                  ON ct.RowNum = r.RowNum - 1)
SELECT *
FROM   cte_Rec  

哪个obv不起作用。

有什么想法吗？

推荐答案

不确定做什么不

但是这样的事情应该起作用：

But something like this should work:

;WITH cte_Rec AS 
(
  SELECT RowNum, RowType, Amount AS 'Amount', Amount AS 'SumAmt'
  FROM dbo.tbl_RecursiveCTE
  WHERE RowType = 'Anch'

  UNION ALL 

  SELECT r.RowNum, r.RowType, r.Amount, CAST(ct.SumAmt - r.Amount AS DECIMAL(18,2))
  from dbo.tbl_RecursiveCTE r
  INNER JOIN cte_Rec ct on ct.RowNum = r.RowNum - 1
)
SELECT  *
FROM cte_Rec

我得到以下输出：

RowNum  RowType  Amount SumAmt
 1      Anch     10.00   10.00
 2      Amt       1.00    9.00
 3      Amt       2.00    7.00
 4      Amt       3.00    4.00
 5      Amt       4.00    0.00

金额行显示该特定行的金额，而 SumAmt 以 10.00 和然后连续减去其他金额-是您要查找的内容吗？

The Amount row shows the amount for that specific row, while SumAmt starts with the 10.00 and then consecutively subtracts the other amounts - is that what you're looking for??

这篇关于递归公用表表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！