如何在查询中使用递归获取父母的所有孩子，然后是他们的孩子 [英] How to get all children of a parent and then their children using recursion in query

问题描述

我有这样的结构:

I have structure like this:

<Unit>
  <SubUnit1>
           <SubSubUnit1/>
           <SubSubUnit2/>
           ...
           <SubSubUnitN/>
  </SubUnit1/>
  <SubUnit2>
           <SubSubUnit1/>
           <SubSubUnit2/>
           ...
           <SubSubUnitN/>
  </SubUnit2/>
  ...
  <SubUnitN>
           <SubSubUnit1/>
           <SubSubUnit2/>
           ...
           <SubSubUnitN/>
  </SubUnitN/>
</Unit>

这个结构有3个层次:main Unit、SubUnits和SubSubUnits.

This structure has 3 levels: main Unit, SubUnits and SubSubUnits.

我想通过 UnitId 选择所有孩子.
如果我按单位搜索，我必须得到所有的树.
如果我按 SubUnit1 搜索，则必须获取 SubUnit1 和 SubUnit1 的所有子项.
如果我搜索 SubSubUnit2，我必须得到自己.

这是我的尝试:

I want to select all children by UnitId.
If I search by Unit, I have to get all tree.
If I search by SubUnit1, I have to get SubUnit1 and all children of SubUnit1.
If I search SubSubUnit2, I have to get itself.

Here is my try:

with a(id, parentid, name)
as (
select id, parentId, name
   from customer a
   where parentId is null 
union all
   select a.id, a.parentid, a.Name
   from customer
     inner join a on customer.parentId = customer.id
    )
select parentid, id, name 
from customer pod
where pod.parentid in (
select id
from customer grbs
where grbs.parentid in (
select id
from customer t
where t.parentid = @UnitId
))
union 
select parentid, id, name
from customer grbs
where grbs.parentid in (
select id
from customer t
where t.parentid = @UnitId
)
union
select parentid, id, name
from customer c
where c.Id = @UnitId
order by parentid, id

我使用了 3 个联合词，虽然效果不佳，但确实有效.案例结构将有N个级别，我如何才能得到正确的结果?

I use 3 union-words, it is not well but it works. Case structure will have N levels, how I have to get correct result?

推荐答案

DECLARE @Id int = your_UnitId
;WITH cte AS 
 (
  SELECT a.Id, a.parentId, a.name
  FROM customer a
  WHERE Id = @Id
  UNION ALL
  SELECT a.Id, a.parentid, a.Name
  FROM customer a JOIN cte c ON a.parentId = c.id
  )
  SELECT parentId, Id, name
  FROM cte

关于 SQLFiddle

这篇关于如何在查询中使用递归获取父母的所有孩子，然后是他们的孩子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！