我有这样的结构:
<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层,我应该如何得到正确的结果?