关于sql:如何获取递归CTE中生成的最后一条记录?

关于sql:如何获取递归CTE中生成的最后一条记录?

How do you get the last record generated in a recursive CTE?

在下面的代码中,我在SQL Server 2005中使用递归CTE(公用表表达式)来尝试查找基本层次结构的顶级父级。 此层次结构的规则是,每个CustID都有一个ParentID,如果CustID没有父级,则ParentID = CustID,它是最高级别。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE @LookupID INT

--Our test value
SET @LookupID = 1

WITH cteLevelOne (ParentID, CustID) AS
(
        SELECT   a.ParentID, a.CustID
        FROM     tblCustomer AS a
        WHERE    a.CustID = @LookupID
    UNION ALL
        SELECT   a.ParentID, a.CustID
        FROM     tblCustomer AS a
        INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID
        WHERE c.CustID <> a.CustomerID
)

因此,如果tblCustomer如下所示:

1
2
3
4
5
ParentID    CustID
5            5
1            8
5            4
4            1

我从上面的代码中得到的结果是:

1
2
3
4
ParentID    CustID
4            1
5            4
5            5

我想要的只是该结果的最后一行:

1
2
ParentID    CustID
5            5

如何返回CTE中生成的最后一条记录(这将是最高级别的CustID)?

另请注意,此表中有多个不相关的CustID层次结构,因此我不能只执行SELECT * FROM tblCustomer WHERE ParentID = CustID。 我无法按ParentID或CustID排序,因为ID号与它在层次结构中的位置无关。


如果您只想获得最高的递归深度,就不能这样做吗?那么,当您实际查询CTE时,只需查找具有max(Depth)的行?像这样:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DECLARE @LookupID INT

--Our test value
SET @LookupID = 1;

WITH cteLevelOne (ParentID, CustID, Depth) AS
(
        SELECT   a.ParentID, a.CustID, 1
        FROM     tblCustomer AS a
        WHERE    a.CustID = @LookupID
    UNION ALL
        SELECT   a.ParentID, a.CustID, c.Depth + 1
        FROM     tblCustomer AS a
        INNER JOIN cteLevelOne AS c ON a.CustID = c.ParentID
        WHERE c.CustID <> a.CustID
)
SELECT * FROM CTELevelone WHERE Depth = (SELECT MAX(Depth) FROM CTELevelone)

或者,根据trevor的建议,可以与同一CTE一起使用:

1
SELECT top 1 * FROM CTELevelone ORDER BY Depth DESC

我认为,在描述的情况下,CustomerID不一定是您想要订购的东西,但是我也不十分清楚这个问题。


我不确定我是否完全理解该问题,但是可以尝试一下:

1
SELECT TOP 1 FROM cteLevelOne ORDER BY CustID DESC

假定CustID也与示例中的顺序相同,而不是类似于GUID。


首先,如果任何父子相同,则不会完成cte。由于它是递归CTE,因此必须终止。如果Parent和cust id相同,则循环不会结束。

消息530,第16层,状态1,第15行
声明终止。在语句完成之前,最大递归100已用尽。


推荐阅读