通过递归查询所有上级(包括本级)
with parent as
(
select id,parentId,Name from JSL_DL
where name like '%呜呜呜%' and IsAvailable like '%%'
union all
select d.id,d.parentId,d.Name from parent p inner join JSL_DL d
on p.parentId= d.id
)
select * from parent
通过递归查询所有下级(包括本级)
with parent as
(
select id,parentId,Name from JSL_DL
where name like '%呜呜呜%' and IsAvailable like '%%'
union all
select d.id,d.parentId,d.Name from parent p inner join JSL_DL d
on p.id= d.parentId
)
select * from parent
其中JSL_DL是表,id是本级id,parentId是上级id
如果要同时查出上级和下级,可以用union联合查询上级和下级,如果有重复可以使用distinct关键字