sql-server-2008 - 如何在查询中使用递归获取父级的所有子级，然后使其子级

我有这样的结构：

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

该结构具有3个级别：主单元，子单元和子子单元。

我想按UnitId选择所有子项。
如果按单位搜索，则必须获取所有树。
如果按SubUnit1搜索，则必须获取SubUnit1和SubUnit1的所有子级。
如果我搜索SubSubUnit2，则必须自行获取。

这是我的尝试：

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个级别，如何获得正确的结果？

最佳答案

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上的演示

关于sql-server-2008 - 如何在查询中使用递归获取父级的所有子级，然后使其子级，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13857329/