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

ゝ一纸荒年。 2022-04-13 04:10 1212阅读 0赞

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

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][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][https_www.cnblogs.com_jx270_p_4258806.html]

[https_blog.csdn.net_wushuo30_article_details_45314905]: https://blog.csdn.net/wushuo30/article/details/45314905
[https_www.cnblogs.com_jx270_p_4258806.html]: https://www.cnblogs.com/jx270/p/4258806.html