SQL Server CTE -Найдите верный родительскийID для каждого дочернего элемента?

Ответ 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 

SE-Data

Это то же самое, что answer marc_s с той разницей, что я использую вашу переменную таблицы и факт, что у вас есть childID = parentID для корневых узлов, где ответ marc_s имеет parent_ID = null для корневых узлов. По-моему, лучше иметь parent_ID = null для корневых узлов.

Ответ 2

Разве вы не можете сделать что-то подобное?

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

Поместите CROSS APPLY после определение CTE - в один оператор SQL, который ссылается на CTE. Разве это не работало?

ИЛИ: - переверните свою логику - сделайте "сверху вниз" 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

Это даст вам узлы что-то вроде этого (на основе ваших данных образца, немного расширенных):

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

Теперь, если вы выберете конкретный дочерний элемент node из этого вывода CTE, вы всегда получите всю необходимую информацию, включая "уровень" дочернего элемента, и его родительский элемент верхнего уровня node.

Ответ 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

Ответ 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  

У меня есть выражение без 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

Рассмотрим данные примера и соответствующий SQL для доступа к дочерним записям вместе со своим главным родителем.

Пример DATA

Код 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