背景

开发过程经常会遇到，需要查询某条记录parent_id相关联的父集数据或子集数据（例如层级数据：地区、菜单、评论、多次回访任务等），可使用mysql8的临时表(with recursive temp as)特性去查询。

1、Mysql 中需要使用 WITH RECURSIVE
2、Oracle数据库中可使用START [Param] CONNECT BY PRIOR

引入

计算1到100的累加的结果。
WITH RECURSIVE t(n) AS ( //t为我们结果表，n为字段，可以只指定表明不指定字段
    VALUES (1) //递归的开始，此时可理解为t表字段n只有一条记录 1
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100      
    /*这里产生的结果为 2  ，此时t表的字段n有两条记录分别为1，2
    *				  3
    *                 ...
    *                 100
    */													   
)
SELECT sum(n) FROM t; //对字段n求和

父查子

WITH RECURSIVE temp AS ( // 将结果表命名为temp
	SELECT * FROM resource r WHERE r.name ='张三'  //查询出父id这条记录，此时这条记录已存在temp表中 ，如图1-1
	UNION ALL
	/*这时要注意，下面这条sql是获取的期望结果中的后两条记录(不包含第一条)
	*注意where后的条件，我们使用temp表中的唯一一条记录的id关联resource表中的pid
	*仅当temp第一条记录匹配不到resource表中的pid时才会对temp的第二条记录id进行匹配
	*/
	SELECT r.* FROM resource r,temp t WHERE t.id = r.pid
)select * from temp

子查父

WITH recursive temp AS (
	SELECT * FROM resource r WHERE r.name ='张三孙子'
	UNION ALL
	//已知的是子集，所以我们需要通过temp的pid匹配resource的id
	SELECT r.* FROM resource r,temp t WHERE t.pid = r.id
)select * from temp

实际运用场景：

 with RECURSIVE temp as(
 select cc.id,cc.parent_id  from t_table cc where id=#{id}
 union
 select t0.id,t0.parent_id  from t_table t0 ,temp t1 where t0.id = t1.parent_id  
 union
 select t0.id,t0.parent_id  from t_table t0 ,temp t1 where t0.parent_id  = t1.id
 )select * from temp

mapper.xml