数据库实现递归查询,获取节点的所有子孙节点

with cte as
(select orgid from fx_org where orgid ='138'
union all
select fx_org.orgid from fx_org  inner join cte on fx_org.parentid = cte.orgid )
select * from cte

这个地方使用的sqlServer的递归写法,如果使用的数据库是mysql的话,就需要下面的一种写法:

with RECURSIVE cte as
(select orgid from fx_org where orgid = '2'
union all
select fx_org.orgid from fx_org  inner join cte on fx_org.parentid = cte.orgid )
select * from cte;

其他的地方没有什么区别,只需要加一个RECURSIVE。

如果需要嵌套在另外的一个sql语句中:

	with cte as
(select orgid from fx_org where orgid ='138'
union all
select fx_org.orgid from fx_org  inner join cte on fx_org.parentid = cte.orgid )

select count(*) from cte inner join zw_zmhd on cte.orgid=zw_zmhd.bm where hdlx ='1'

这里的cte就像是一个新的表格

上一篇:OpenCv 014---图像插值(Image interpolation)


下一篇:使用MQTT.fx客户端接入阿里云