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就像是一个新的表格