tsql 递归选择

Question

我有一个表，其下一个结构

Id|State|Quan|SellQuan|LastId
3 |2    | 5  | 0      |2
2 |3    |10  | 5      |1
1 |3    |15  | 5      |NULL

LastId 在 Id 字段上引用，我需要在下一步中计算值：从 Id=3 中获取 Quan，然后添加所有在先前 LastId 上引用的 Id 的 sellquan。就是这样 Id=3 - 最后一个Id = 2 =>从 Id=2 字段 sellquan 中获取值，然后检查 LastId 是否不为空 .在我的例子中LastId = 1 =>取Id=1值SellQaun 结果：5 + 5+ 5 = 15

Answer 1

我不太确定你想要什么。但我会尝试一下。

首先是一些测试数据：

DECLARE @tbl TABLE(Id INT,State INT,Quan INT,SellQuan INT,LastId INT)
INSERT INTO @tbl
VALUES
    (3,2,5,0,2),
    (2,3,10,5,1),
    (1,3,15,5,NULL)

然后是这样的递归函数：

DECLARE @Id INT=1
;WITH CTE(Id,parentId,SellQuan,topParent)
AS
(
    SELECT
        tbl.Id,
        tbl.LastId AS parentId,
        (CASE 
            WHEN EXISTS(SELECT NULL FROM @tbl AS tbl2 WHERE tbl2.LastId=tbl.Id)
            THEN tbl.SellQuan
            ELSE tbl.Quan
        END) AS SellQuan,
        tbl.Id AS topParent
    FROM
        @tbl AS tbl
    WHERE
        tbl.Id=@Id
    UNION ALL
    SELECT
        tbl.Id,
        tbl.LastId AS parentId,
        (CASE 
            WHEN EXISTS(SELECT NULL FROM @tbl AS tbl2 WHERE tbl2.LastId=tbl.Id)
            THEN tbl.SellQuan
            ELSE tbl.Quan
        END) AS SellQuan,
        CTE.topParent AS topParent
    FROM
        @tbl AS tbl
        JOIN CTE
            ON CTE.Id=tbl.LastId
)
SELECT
    CTE.topParent AS id,
    SUM(CTE.SellQuan) AS SellQaunResult
FROM
    CTE
GROUP BY
    CTE.topParent

希望这有帮助

Answer 2

根据您的要求，我认为您的示例表中有错字。如果您对以下内容进行更改，则会改变 CTE 的复杂性：

Id|State|Quan|SellQuan|LastId
3 |2    | 5  | 5      |2
2 |3    |10  | 5      |1
1 |3    |15  | 5      |NULL

我进行该更改，它会简化 CTE。

DECLARE @tbl TABLE(Id INT,State INT,Quan INT,SellQuan INT,LastId INT)
INSERT INTO @tbl
VALUES
(3,2,5,5,2),
(2,3,10,5,1),
(1,3,15,5,NULL);

;WITH CTE(Id,total,depth)
AS (
SELECT tbl.Id, SellQuan as total, 0 as depth
  FROM @tbl AS tbl
 WHERE tbl.LastId is null
 UNION ALL -- Recursive part of the query
SELECT tbl.Id,
       tbl.SellQuan + CTE.total,
       CTE.depth + 1 as depth
  FROM @tbl AS tbl
 INNER JOIN CTE ON CTE.Id = tbl.LastId
)
SELECT top 1 total
FROM CTE
order by depth desc;

我希望这有帮助。有关 CTE 的更多信息，我的网站上有几篇文章：
http://stevestedman.com/cte/