SQL Server CTE - 查找每个childID的*parentID？

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

desired output:

期望的输出：

I've created a recursive CTE which finds me the top fatherID.

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

Something like:

就像是：

;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
        )

The problem:

问题：

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以开始（214）！所以它只给我214的价值。 CTE执行递归部分并找到childID的topParent。

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

但我希望表中的ForEach行 - 用childID值执行CTE！

I have tried to do it with CROSS APPLY:

我试过用CROSS APPLY来做：

Something like:

就像是：

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

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

但恕我直言（从我的测试!!） - 这是不可能的。

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

将递归CTE放入UDF的另一个想法是性能损失（我们知道udf的问题）。

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

如何创建此查询以使其实际工作？ （或一些接近解决方案）？

here is what I've tried

这是我尝试过的

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

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

9 个解决方案

#1

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-Data

SE-数据

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.

它与marc_s的回答相同，不同之处在于我使用了表变量，并且对于根节点有childID = parentID这一事实，其中marc_s的回答对于根节点具有parent_ID = null。在我看来，对于根节点，最好使用parent_ID = null。

#2

Can't you do something like this?

你不能这样做吗？

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

Put the CROSS APPLY after the CTE definition - into the one SQL statement that refers back to the CTE. Wouldn't that work??

将CTE定义之后的CROSS APPLY放入一个引用回CTE的SQL语句中。那会不会工作？

OR: - flip around your logic - do a "top-down" CTE, that picks the top-level nodes first, and then iterates through the hiearchy. This way, you can easily determine the "top-level father" in the first part of the recursive CTE - something like this:

或者： - 绕过你的逻辑 - 做一个“自上而下”的CTE，首先选择*节点，然后遍历hiearchy。这样，您可以在递归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

This would give you nodes something like this (based on your sample data, slightly extended):

这将为您提供类似这样的节点（基于您的示例数据，稍微扩展）：

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

Now if you select a particular child node from this CTE output, you'll always get all the infos you need - including the "level" of the child, and its top-level parent node.

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

#3

I have not yet the time to look further into your question and am not sure whether or not i've understood your problem, but couldn't you use this svf to get the top father's id?

我还没有时间深入研究你的问题而且我不确定我是否理解你的问题，但是你不能用这个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

Then you should be able to find each top parent in this way:

那么你应该能够以这种方式找到每个*父母：

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

#4

select distinct 
       a.ChildID,a.ParentID,
       --isnull(nullif(c.parentID,b.parentID),a.parentID) as toppa,
       B.parentID
       --,c.parentID
      ,isnull(nullif(d.parentID,a.parentID),c.parentID) as toppa1,a.name
from myTable a
   inner join myTable c
       on a.parentID=c.parentID
   inner join myTable b
       on b.childID=a.parentID
   inner join myTable d
       on d.childID=b.parentID  

I have using the without CTE expression and then using joins to get the step to step parent for child and then more important Common table expressions were introduced in SQL Server 2005 not in server 2000 so using joins to get values this is basic way for to get parentid for a child value

我使用了没有CTE表达式，然后使用连接来获取子步骤的父步骤然后更重要的公用表表达式在SQL Server 2005中而不是在服务器2000中引入，因此使用连接来获取值这是获取的基本方法parentid为孩子的价值

#5

select dbo.[fn_getIMCatPath](8)
select Cat_id,Cat_name,dbo.[fn_getIMCatPath](cat_id) from im_category_master

Create FUNCTION [dbo].[fn_getIMCatPath] (@ID INT) 
returns NVARCHAR(1000) 
AS 
BEGIN 
  DECLARE @Return   NVARCHAR(1000), 
          @parentID INT, 
          @iCount   INT 

  SET @iCount = 0 

  SELECT @Return = Cat_name, 
         @parentID = parent_id 
  FROM   im_category_master 
  WHERE  [cat_id] = @ID 

  WHILE @parentID IS NOT NULL 
    BEGIN 
        SELECT @Return = cat_name + '>' + @Return, 
               @parentID = parent_id 
        FROM   im_category_master 
        WHERE  [cat_id] = @parentID 

        SET @iCount = @iCount + 1 
        IF @parentID = -1
        BEGIN
        SET @parentID = NULL 
        END
        IF @iCount > 10 
          BEGIN 
              SET @parentID = NULL 
              SET @Return = '' 
          END 
    END 

  RETURN @Return 
END

#6

Consider this sample data and respective SQL to access child records along with their top parent.

考虑此示例数据和相应的SQL以访问子记录及其*父级。

Sample DATA

样本数据

SQL code:

SQL代码：

;WITH c AS (
   SELECT Id, Name, ParentId as CategoryId, 
          Id as MainCategoryId, Name AS MainCategory 
     FROM   pmsItemCategory 
     WHERE  ParentId is null

     UNION ALL 

     SELECT T.Id, T.Name, T.ParentId,  MainCategoryId, MainCategory 
     FROM   pmsItemCategory AS T 
            INNER JOIN c  ON T.ParentId = c.Id 
     WHERE  T.ParentId is not null
    ) 

SELECT Id, Name, CategoryId, MainCategoryId, MainCategory 
FROM   c 
order by Id

#7

select distinct 
       a.ChildID,a.ParentID,
       --isnull(nullif(c.parentID,b.parentID),a.parentID) as toppa,
       B.parentID
       --,c.parentID
      ,isnull(nullif(d.parentID,a.parentID),c.parentID) as toppa1,a.name
from myTable a
   inner join myTable c
       on a.parentID=c.parentID
   inner join myTable b
       on b.childID=a.parentID
   inner join myTable d
       on d.childID=b.parentID

#8

With cte as 
(
Select ChileId,Name,ParentId from tblHerarchy
where ParentId is null 
union ALL
Select h.ChileId,h.Name,h.ParentId  from cte
inner join tblHerarchy h on h.ParentId=cte.ChileId
) 
Select * from cte

#9

With cteherarchy as 
(
Select ChileId,Name,ParentId from tblHerarchy
where ParentId is null 
union ALL
Select h.ChileId,h.Name,h.ParentId  from cte
inner join tblHerarchy h on h.ParentId=cte.ChileId
) 
Select * from cteherarchy