首页 / 知识

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

2023-04-13 16:13:00

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一起使用：