Server -Find parentID forEach childID？

Question

我有一个包含层次数据的表--类似于：

childID  |  parentID
____________________
  1      |     5
  5      |     9
  9      |     20
  2      |     4
  3      |     7
  7      |     8
  8      |     8
 20      |     20
  4      |     4
  8      |     8

期望产出：

​

​

我创建了一个递归的CTE，它为我找到了顶级的fatherID。

类似于：

;WITH cte AS (
                 SELECT a.childID
                       ,a.parentID
                       ,1 AS lvl
                 FROM   [Agent_Agents] a
                 WHERE   a.childID = 214 //<==== value to begin with !! - thats part the problem
                 UNION ALL
                 SELECT tmp.childID
                       ,tmp.parentID
                       ,cte.lvl+1
                 FROM   [Agent_Agents] tmp
                         INNER JOIN cte  ON  tmp.childID = cte.parentID
                 WHERE   cte.childID<>cte.parentID
             )
SELECT *
FROM   cte
WHERE   lvl = (
            SELECT MAX(lvl)
            FROM   cte
        )

问题：

我使用显式 childID值执行CTE，以(214)开始！所以它给了我214的价值。CTE执行递归部分，并为topParent查找childID。

但是，我想要ForEach row in the Table --用childID值执行CTE！

我试着用CROSS APPLY做了：

类似于：

select * from myTable Cross Apply (
                                     ;WITH cte AS (....)
                                  )

但IMHO (从我的测试!!) -这是不可能的。

将递归CTE放入UDF中的另一种想法有性能损失(我们所知道的udf问题)。

我如何创建这个查询以使其实际工作？(或一些接近的解决方案)？

这是我尝试过的

https://data.stackexchange.com/stackoverflow/query/edit/69458

Answer 1

我不知道你在找什么，但可能是这个。

;WITH c 
     AS (SELECT childid, 
                parentid, 
                parentid AS topParentID 
         FROM   @myTable 
         WHERE  childid = parentid 
         UNION ALL 
         SELECT T.childid, 
                T.parentid, 
                c.topparentid 
         FROM   @myTable AS T 
                INNER JOIN c 
                        ON T.parentid = c.childid 
         WHERE  T.childid <> T.parentid) 
SELECT childid, 
       topparentid 
FROM   c 
ORDER  BY childid 

硒数据

与回答 by S一样，我使用表变量的不同之处在于，对于根节点有childID = parentID，而marc_s的答案对于根节点有parent_ID = null。在我看来，最好为根节点设置parent_ID = null。

Answer 2

你就不能这样做吗？

;WITH cte AS (....)
SELECT
    * 
FROM 
    cte
CROSS APPLY 
    dbo.myTable tbl ON cte.XXX = tbl.XXX

将CROSS APPLY 放在之后-- CTE定义--放到引用CTE的一个SQL语句中。那不管用吗？

OR: --翻转您的逻辑--做一个“自上而下”的CTE，先选择顶级节点，然后遍历这个组织。这样，您就可以很容易地在递归CTE的第一部分中确定“顶级父亲”--如下所示：

;WITH ChildParent AS
(
    SELECT
        ID,
        ParentID = ISNULL(ParentID, -1),
        SomeName, 
        PLevel = 1,   -- defines level, 1 = TOP, 2 = immediate child nodes etc.
        TopLevelFather = ID  -- define "top-level" parent node
    FROM dbo.[Agent_Agents] 
    WHERE ParentID IS NULL

    UNION ALL

    SELECT
        a.ID,
        ParentID = ISNULL(a.ParentID, -1),
        a.SomeName, 
        PLevel = cp.PLevel + 1,
        cp.TopLevelFather   -- keep selecting the same value for all child nodes
    FROM dbo.[Agent_Agents] a
    INNER JOIN ChildParent cp ON r.ParentID = cp.ID
)
SELECT  
   ID,
   ParentID,
   SomeName,
   PLevel,
   TopLevelFather   
FROM ChildParent

这将为节点提供类似的内容(基于示例数据，略有扩展)：

ID  ParentID  SomeName      PLevel  TopLevelFather
20    -1      Top#20           1          20
 4    -1      TOP#4            1           4
 8    -1      TOP#8            1           8
 7     8      ChildID = 7      2           8
 3     7      ChildID = 3      3           8
 2     4      ChildID = 2      2           4
 9    20      ChildID = 9      2          20
 5     9      ChildID = 5      3          20
 1     5      ChildID = 1      4          20

现在，如果您从这个CTE输出中选择一个特定的子节点，您将始终获得所需的所有信息--包括子节点的“级别”及其顶级父节点。

Answer 3

我还没有时间进一步研究你的问题，也不确定我是否理解你的问题，但你不能用这个svf来得到顶级父亲的身份证吗？

CREATE FUNCTION [dbo].[getTopParent] (
    @ChildID INT
)

RETURNS int
AS
BEGIN
    DECLARE @result int;
    DECLARE @ParentID int;

    SET @ParentID=(
        SELECT ParentID FROM ChildParent
        WHERE ChildID = @ChildID 
    )

    IF(@ParentID IS NULL)
        SET @result = @ChildID 
    ELSE
        SET @result = [dbo].[getTopParent](@ParentID)

    RETURN @result    
END

然后，您应该能够这样找到每个顶级家长：

SELECT ChildID
    ,  [dbo].[getTopParent](ChildID) AS TopParentID
FROM ChildParent