实际业务中经常会使用到无限级分类,当我们需要根据一个指定分类推算出它下面的所有子级,包括子级的子级等,或者推算出所有父级,包括父级的父级等,我一般是通过后端代码来递归推算。其实也可以通过SQL语句来实现,免去写递归代码。不多废话,上SQL。
一、推算所有父级
with cte_parent(Id,Name,ParentId)
as
(
select Id,Name,ParentId from DeviceTree where Id= '{groupid}'
union all
select a.Id,a.Name,a.ParentId from DeviceTree as a inner join cte_parent as b
on a.Id=b.ParentId where a.ParentId!='00000000-0000-0000-0000-000000000000' and a.isdelete=0
)
select Id,Name from cte_parent;
二、推算所有子级
with cte_child(Id,Name,ParentId)
as
(
select Id,Name,ParentId from DeviceTree where Id = '{groupid}'
union all
select a.Id,a.Name,a.ParentId from DeviceTree a
inner join cte_child b on ( a.ParentId=b.Id) where a.isdelete=0
)
select Id,Name from cte_child;
以上代码未做性能测试,仅供参考。