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
————————————————
版权声明：本文为CSDN博主「书剑零落」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/shunhua19881987/article/details/84561497