mysql8 递归查询父子集(临时表with recursive temp as)

背景

开发过程经常会遇到,需要查询某条记录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.xmlmysql8 递归查询父子集(临时表with recursive temp as)

上一篇:Git clone --recursive


下一篇:Day 13 生成器,函数补充