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

desired output:

I've created a recursive CTE which finds me the top 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.

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

I have tried to do it with 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).

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/stackoverflow/query/edit/69458

回答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

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.

回答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??

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:

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

回答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?

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

回答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.

Sample DATA

SQL code:

;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