SQL Server CTE-为每个子ID查找顶部的父ID？ [英] SQL Server CTE -Find top parentID forEach childID?

问题描述

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

I have a table which contains hierarchy data - something like:

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

所需的输出：

我创建了一个递归CTE

I've created a recursive CTE which finds me the top 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 value以（214）开头！
所以它只给我214的价值。
CTE进行递归部分并找到childID的topParent。

I executed the CTE with explicit childID value to begin with (214) ! So it gives me the value for 214 only. the CTE do the recursive part and find topParent for childID.

但是
我要表中的每一行-用 childID 值执行CTE！

but I want ForEach row in the Table - to execute the CTE with the childID value !

我尝试使用 CROSS APPLY 来做到这一点：

I have tried to do it with CROSS APPLY:

类似的东西：

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

但是恕我直言（根据我的测试！）-这是不可能的。

but IMHO (from my testing !!) - its impossible.

将递归CTE放在UDF中的另一种想法会降低性能（众所周知的udf问题）。

The other idea of putting the recursive CTE in a UDF has a performance penalty (udf's problem as we know).

如何创建此查询，使其真正起作用？ （或一些附近的解决方案）？

How can I create this query so that it'll actually work? ( or some near solution )?

这是我尝试过的内容

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

推荐答案

不确定我了解您在寻找什么，但是可能就是这个。

Not sure I understand what you are looking for but it could be this.

;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 

SE数据

它与答案相同。 com / users / 13302 / marc-s> marc_s ，不同之处在于我使用了您的表变量，并且您拥有根节点的 childID = parentID 其中marc_s的答案对于根节点具有 parent_ID = null 。我认为最好为根节点使用 parent_ID = null 。

It is the same as answer by marc_s with the difference that I use your table variable and the fact that you have childID = parentID for root nodes where the answer by marc_s has parent_ID = null for root nodes. In my opinion it is better to have parent_ID = null for root nodes.

这篇关于SQL Server CTE-为每个子ID查找顶部的父ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！