如何使用id和parentId遍历表中的路径？

Question

假设我有一个类似于的表

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

我正在尝试编写一个标量函数，我可以调用为：

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

1. 查找"A"的ID，该ID为1
2. 查找父级为"A"（ID:1）的"B"的ID，该ID应为ID:2
3. 查找父级为"B"（ID:2）的"C"的ID，该ID应为ID:3
4. 查找父级为"C"（ID:3）的"E"的ID，该ID应为ID:5

我试着用WHILE循环来做，但它变得非常复杂，非常快。。。只是觉得必须有一个简单的方法来做到这一点。

Answer 1

CTE版本不是获取分层数据的优化方式。（参考MSDN博客）

你应该做下面提到的事情。它测试了1000万条记录，比CTE版本快300倍：）

Declare @table table(Id int, ParentId int, Name varchar(10))
insert into @table values(1,NULL,'A')
insert into @table values(2,1,'B')
insert into @table values(3,2,'C')
insert into @table values(4,1,'E')
insert into @table values(5,3,'E')
DECLARE @Counter tinyint = 0;
IF OBJECT_ID('TEMPDB..#ITEM') IS NOT NULL
DROP TABLE #ITEM
CREATE TABLE #ITEM
(
 ID int not null 
,ParentID int
,Name VARCHAR(MAX)
,lvl int not null
,RootID int not null
)
INSERT INTO #ITEM
    (ID,lvl,ParentID,Name,RootID)
SELECT   Id
        ,0 AS LVL
        ,ParentId
        ,Name
        ,Id AS RootID
FROM            
    @table
WHERE
        ISNULL(ParentId,-1) = -1
WHILE @@ROWCOUNT > 0
    BEGIN
        SET @Counter += 1
        insert into #ITEM(ID,ParentId,Name,lvl,RootID)
        SELECT  ci.ID
                ,ci.ParentId
                ,ci.Name
                ,@Counter as cntr
                ,ch.RootID
        FROM    
            @table AS ci
        INNER JOIN
            #ITEM AS pr 
        ON
            CI.ParentId=PR.ID
        LEFT OUTER JOIN
            #ITEM AS ch
        ON  ch.ID=pr.ID
        WHERE           
                ISNULL(ci.ParentId, -1) > 0
            AND PR.lvl = @Counter - 1
END
select * from #ITEM

Answer 2

下面是一个基于示例数据和我所理解的需求的功能性rcte示例。

if OBJECT_ID('tempdb..#Something') is not null
    drop table #Something
create table #Something
(
    id int
    , parentId int
    , name char(1)
)
insert #Something
select 1, NULL, 'A' union all
select 2, 1, 'B' union all
select 3, 2, 'C' union all
select 4, 1, 'E' union all
select 5, 3, 'E'
declare @Root char(1) = 'A';
with MyData as
(
    select *
    from #Something
    where name = @Root
    union all
    select s.*
    from #Something s
    join MyData d on d.id = s.parentId
)
select *
from MyData

请注意，如果更改变量的值，输出将进行调整。我会使它成为一个内联表值函数。

Answer 3

我想我是根据@SeanRange的建议使用递归CTE的（上面的评论）：

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