根据父ID递归获取所有数据 [英] Recursively grab all data based on a parent id

问题描述

我们有一个表格，其中的行递归地链接到另一行.我想提取与给定 parentId 及其所有子项关联的数据.其中 parentId 是根行中的一个.

We have a table where rows recursively link to another row. I want to pull data associated with a given parentId and all it's children. Where parentId is one from the root row.

我以为我以前见过或做过类似的事情，但现在找不到了.这可以在 SQL 中完成还是在代码中更好?

I thought I have seen or done something like that before, but I am unable to find it now. Can this be done in SQL or is it better to do this in code?

我希望列表在我完成后看起来像这样:

I want the list to look like this when I'm done:

• 父母
  • 孩子
  • • 孙子

    推荐答案

    这可以在 SQL Server 2005 及更高版本中使用公共表表达式 (CTE) 完成.这是 MSDN 中描述递归查询的一个很好的链接:Recursive Queries Using Common Table Expressions

    This can be done in SQL Server 2005 and above using Common Table Expressions (CTEs). Here is a great link from MSDN describing recursive queries: Recursive Queries Using Common Table Expressions

    这是一个例子:

    如果您想象一个人的分层行，此查询将让您查看任何人的完整行并计算他们在层次结构中的位置.可以修改它以查找任何子关系.

    If you imagine a hierarchical line of people, this query will let you see the complete line of any person AND calculates their place in the hierarchy. It can be modified to find any child relationship.

    您交换的是您用作父级的行的 ID，而不是此人的 ID.

    Instead of the ID of the person, you swap in the ID of the row you are using as your parent.

    --Create table of dummy data
    create table #person (
    personID integer IDENTITY(1,1) NOT NULL,
    name      varchar(255) not null,
    dob       date,
    father    integer
    );
    
    INSERT INTO #person(name,dob,father)Values('Pops','1900/1/1',NULL);  
    INSERT INTO #person(name,dob,father)Values('Grandma','1903/2/4',null);
    INSERT INTO #person(name,dob,father)Values('Dad','1925/4/2',1);
    INSERT INTO #person(name,dob,father)Values('Uncle Kev','1927/3/3',1);
    INSERT INTO #person(name,dob,father)Values('Cuz Dave','1953/7/8',4);
    INSERT INTO #person(name,dob,father)Values('Billy','1954/8/1',3);
    
    DECLARE @OldestPerson INT; 
    SET @OldestPerson = 1; -- Set this value to the ID of the oldest person in the family
    
    WITH PersonHierarchy (personID,Name,dob,father, HierarchyLevel) AS
    (
       SELECT
          personID
          ,Name
          ,dob
          ,father,
          1 as HierarchyLevel
       FROM #person
       WHERE personID = @OldestPerson
    
       UNION ALL
    
       SELECT
        e.personID,
          e.Name,
          e.dob,
          e.father,
          eh.HierarchyLevel + 1 AS HierarchyLevel
       FROM #person e
          INNER JOIN PersonHierarchy eh ON
             e.father = eh.personID
    )
    
    SELECT *
    FROM PersonHierarchy
    ORDER BY HierarchyLevel, father;
    
    DROP TABLE #person;
    

    这篇关于根据父ID递归获取所有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！