SQL SERVER 根据子节点查询所有父节点、根据父节点获取所有子节点

一、根据表单类型查询其所有的上级表单类型：

1、每次按照一个子节点的值进行查询：

with partents as
(
select * from JCBDFL where BDFLDM ='14'

UNION ALL
 
(SELECT a.* from JCBDFL as a 
    inner join
  partents as b on a.BDFLDM=b.SJDM
 )

)
SELECT BDFLDM,BDFLMC, SJDM FROM partents ;

其中14是子节点的值，根据实际情况进行修改。

2、按照多个子节点的值进行查询：

with partents as
(
select * from JCBDFL where BDFLDM in('14','16')
UNION ALL
 (SELECT a.* from JCBDFL as a inner join
  partents as b on a.BDFLDM=b.SJDM
 )
)
SELECT distinct BDFLDM,BDFLMC, SJDM FROM partents ;

其中14、16是子节点的值，根据实际情况进行修改。

参照网址：https://blog.csdn.net/wushuo30/article/details/45314905

二、根据父节点获取所有子节点

with cte(id,parentid,text)
as
(--父项
select id,parentid,text from treeview where parentid = 450
union all
--递归结果集中的下级
select t.id,t.parentid,t.text from treeview as t
inner join cte as c on t.parentid = c.id
)
select id,parentid,text from cte

参数网址：https://www.cnblogs.com/jx270/p/4258806.html