Server 2019中的父-子关系

Question

我有一张桌子可以存储这样的关系：

ParentName ParentID ChildName ChildId
-------------------------------------
Name1      Guid     NameA      Guid
Name2      Guid     NameB      Guid
Name3      Guid     NameC      Guid
NameA      Guid     NameY      Guid
NameB      Guid     NameX      Guid
NameC      Guid     NameZ      Guid
NameY      Guid     Name1A     Guid

我的目标是检索数据堆叠的方式，无论在层次结构的很远，子列显示谁是最高的父级。

我知道这是一个递归的CTE，但我不知道如何将查询中的递归联接写回锚点，而不会中断最大的递归数。父查询使用WHERE子句有78条记录，但总级别不应超过4或5。父级可以有多个子级。

Answer 1

下面的查询应该可以工作。虽然它受到数据库允许的最大递归，但在SQL服务器的情况下，默认情况下是100。因此，它应该工作到许多层次的嵌套父母-子女关系。

WITH RECURSIVE cte AS (
    SELECT ChildID,ChildName,ParentID,ParentName, 1 AS Level FROM parents

    UNION ALL

    SELECT cte.ChildID,
        cte.ChildName,
        GrandParent.ParentID,
        GrandParent.ParentName,
        Level+1 
    FROM cte 
    INNER JOIN parents AS GrandParent 
    ON cte.ParentID=GrandParent.ChildID 
)
SELECT ChildID,ChildName,ParentID,ParentName 
FROM(
    SELECT cte.*,
        ROW_NUMBER() OVER(PARTITION BY ChildID,ChildName ORDER BY Level DESC) AS rnk 
    FROM cte
) Rank_Highest_Level
WHERE rnk=1
ORDER BY ChildID;

以下是DB Fiddle中的工作实例。

Answer 2

下面是这个递归查询的一个工作示例。

CREATE TABLE parents (
    ParentName char(10),
    ParentID char(10),
    ChildName char(10),
    ChildId char(10) );
insert into parents values( 'a',1,'b',2);
insert into parents values( 'b',2,'c',3);
insert into parents values( 'c',3,'d',4);
insert into parents values( 'd',4,'e',5);
insert into parents values( 'e',5,'f',6);
insert into parents values( 'f',6,'g',7);
insert into parents values( 'g',7,'h',8);

select 
    p1.pname, p1.pid,
    p2.pname, p2.pid,
    p3.pname, p3.pid,
    p4.pname, p4.pid,
    p5.pname, p5.pid,
    p5.cname, p5.cid
from
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p1 right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p2 on p1.cid=p2.pid right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p3 on p2.cid=p3.pid right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p4 on p3.cid=p4.pid right join
    (select ParentName pname, ParentID pid, ChildName cname, ChildId cid from parents) p5 on p4.cid=p5.pid 
order by
    p1.pid,p2.pid,p3.pid,p4.pid,p5.pid;
DROP TABLE parents;
``