递归查询以查找父记录 [英] Recursive query to find the parent record

问题描述

基于最高级别和对应的childID记录即这里的 71，我需要将级别一直上升到 1 并获得对应的 childID 记录，即 209

Based on the highest level and for the corresponding childID record i.e. 71 here, I need to go up the level all the way to 1 and get the corresponding childID record i.e. 209

例如:

要查找 71 的子记录:

To find the childrecord for 71:

4 级父级 - 154，3 级父级 - 192，2 级父级 - 209或 1 级孩子 - 209

level4 parent - 154, level3 parent - 192, level2 parent - 209 or level1 child - 209

209 是需要的答案.

209 is the needed answer.

现在棘手的部分是最高级别是可变的.我的查询当级别增加到 6 或 7 时，上面显示的不起作用不知道需要的连接数.

Now the tricky part is that the highest level is variable. My query shown above doesn't work as the level increases to 6 or 7 as I will not know the number of joins needed.

我们可以在递归 CTE 中轻松做到这一点吗?

Can we do this easily in recursive CTE?

<代码>

declare @t table (
 childID int,
 ParentID int,
 level int
)

insert into @t
select 71, 154, 4
union
select 154, 192, 3
union
select 192, 209, 2
union
select 209, 0, 1

select * from @t

select t1.childID, t4.ChildID
from @t t1
inner join
@t t2
on t1.ParentID = t2.childID
inner join
@t t3
on t2.ParentID = t3.childID
inner join
@t t4
on t3.ParentID = t4.childID
and t1.childID = 71

-- 我尝试使用递归 CTE

-- I tried to with recursive CTE

-- I need to get 71, 209 but getting 209, 0


  ;with MyCTE as
    (
    select childID, ParentID from @t t1 
    where t1.level = 1
    UNION ALL
    select m.childID, t2.childID from @t t2
    inner join
    MyCTE m
    on m.childID = t2.ParentID
    )
select top 1 * from MyCTE

推荐答案

试试这个:

declare @t table (
 childID int,
 ParentID int,
 level int
)

insert into @t
select 71, 154, 4
union
select 154, 192, 3
union
select 192, 209, 2
union
select 209, 0, 1

Declare @SearchChild int
set @SearchChild=71

  ;with MyCTE as (
      select t1.childID, t1.ParentID , @SearchChild AS searchChild, t1.level
        from @t t1 
        where t1.childID = @SearchChild
      UNION ALL
      select t1.childID, t1.ParentID , c.SearchChild, t1.level
        from @t t1
        inner join MyCTE c on t1.childID=c.ParentID
  )
select top 1 * from MyCTE order by level asc

输出:

childID     ParentID    searchChild level
----------- ----------- ----------- -----------
209         0           71          1

我不知道你在追求什么，没有 209 和 71 放在一起的行吗?这是你能做的最好的事情.此外，这个 CTE 可以在链上而不是向下工作，并且在大表上应该工作得更好.

I'm not sure what you are after, there is no row that has 209 and 71 together? this is the best you can do. Also, this CTE works up the chain and not down, and should work much better on large tables.

这篇关于递归查询以查找父记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！