mysql递归 WITH recursive的用法

语法

WITH recursive 表名 AS ( 
	初始语句（非递归部分） 
	UNION ALL 
	递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]

使用示例
该表是树结构

with recursive temp as (

SELECT * FROM t_department where id ='402880fc8726dec2018726e11e3407ba'
UNION all
SELECT a.* FROM t_department a ,temp t where  a.id = t.parent_id 
)    
select * from temp;

也可以用join来显示结果都一样

with recursive temp as (

SELECT * FROM t_system_department where id ='402880fc8726dec2018726e11e3407ba'
UNION all
SELECT a.* FROM t_system_department a join temp on a.id = temp.parent_id 
)    
select * from temp