如何使用递归在查询中获取父级及其所有子级的信息

Question

我有这样的结构：

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

这个结构有3个层级：主单元，子单元和子子单元。

我想通过UnitId选择所有的子节点。
如果我按Unit搜索，则必须获取整棵树。
如果我按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个联合词，虽然不是很好但它能用。Case结构将有N层，我应该如何得到正确的结果？

- user1893999

---

请看这个答案：https://dev59.com/D3RC5IYBdhLWcg3wYP6h - twoleggedhorse

Answer 1

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 上进行演示。

- Aleksandr Fedorenko

Answer 2

如果父ID是其本身的子级，则需要使用不同的查询。例如，模式结构如下：

CREATE TABLE customer
(
  id int,
  parentid int,
  name nvarchar(10)
)

INSERT customer
VALUES(1,  1, 'aaa'),
  (2,  1,    'bbb'),
  (3,  2,    'ccc'),
  (4,  2,    'ddd'),
  (5,  1,    'eee'),
  (6,  5,    'fff'),
  (7,  5,    'ggg'),
  (8,  8,    'hhh'),
  (9,  8,    'iii'),
  (10, 8,    'jjj')

在这种情况下，我们需要使用以下查询：

DECLARE @Id int = 1 -- your UnitId
;WITH cte AS 
 (
  SELECT a.Id, a.parentId, a.name
  FROM customer a
  WHERE parentid = @Id
  UNION ALL
  SELECT a.Id, a.parentid, a.Name
  FROM customer a JOIN cte c ON a.parentId = c.id
   and c.id != @Id

  )
  SELECT parentId, Id, name
  FROM cte
go

- user2231692