如何遍历ID为&的表中的路径parentId? [英] How to traverse a path in a table with id & parentId?

问题描述

假设我有一个像这样的表:

Suppose I have a table like:

id    |   parentId   |  name
 1          NULL         A
 2            1          B
 3            2          C
 4            1          E
 5            3          E

我正在尝试编写一个标量函数，我可以这样称呼它:

I am trying to write a scalar function I can call as:

SELECT dbo.GetId('A/B/C/E')如果使用上面的参考表，它将产生"5".该函数将执行以下步骤:

SELECT dbo.GetId('A/B/C/E') which would produce "5" if we use the above reference table. The function would do the following steps:

1. 找到ID为1的"A"
2. 找到其父代为"A"(即ID:1)的ID"B"，即ID:2
3. 找到其父级为"B"的ID"C"(ID:2)，即ID:3
4. 找到其父代为"C"的ID"E"(id:3)，其ID为5:

1. Find the ID of 'A' which is 1
2. Find the ID of 'B' whose parent is 'A' (id:1) which would be id:2
3. Find the ID of 'C' whose parent is 'B' (id:2) which would be id:3
4. Find the ID of 'E' whose parent is 'C' (id:3) which would be id:5

我试图通过WHILE循环来实现它，但是它变得非常非常非常复杂...只是认为必须有一种简单的方法来实现它.

I was trying to do it with a WHILE loop but it was getting very complicated very fast... Just thinking there must be a simple way to do this.

推荐答案

我认为我基于@SeanLange的建议使用递归CTE(在注释上方):

I think I have it based on @SeanLange's recommendation to use a recursive CTE (above in the comments):

CREATE FUNCTION GetID 
(
    @path VARCHAR(MAX)
)

/* TEST:
SELECT dbo.GetID('A/B/C/E')

*/
RETURNS INT 
AS
BEGIN
    DECLARE @ID INT;

    WITH cte AS (
        SELECT p.id ,
               p.parentId ,
               CAST(p.name AS VARCHAR(MAX)) AS name
        FROM tblT p
        WHERE parentId IS NULL

        UNION ALL
        SELECT p.id ,
               p.parentId ,
               CAST(pcte.name + '/' + p.name AS VARCHAR(MAX)) AS name
        FROM dbo.tblT p
        INNER JOIN cte pcte ON
            pcte.id = p.parentId
    )
    SELECT @ID = id
    FROM cte
    WHERE name = @path

    RETURN @ID
END

这篇关于如何遍历ID为&的表中的路径parentId?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！