MySql递归RECURSIVE的详解

article/2025/8/25 2:33:02

背景：

在实际开发的过程中，我们会遇到一些数据是层级关系的、要展示数据子父级关系的时候，
第一个解决方案：将数据库中的所有数据都查询出来用Java代码进行处理。
第二个解决方案：可以考虑MySql中的RECURSIVE递归进行数据的查询。
具体选择什么样的方法去实现，结合自己项目需求考虑。

目前有这样的一张关系属性表：
在这里插入图片描述

需求1 ：找到名字为“隔壁老王”所有的后代（父查子）

	-- 递归找出隔壁老王所有的后代(父找子)WITH RECURSIVE temp(ID,USERNAME,PID,pName) AS (-- 将结果表命名为temp-- 查询出父id这条记录，此时这条记录已存在temp表中	SELECT  KI.ID,KI.NAME,KI.PID, KI.NAME FROM KINSHIP_INFO KI WHERE KI.`NAME`= '隔壁老王'UNION ALL/*ps：下面这条sql是获取的期望结果中的后两条记录(不包含第一条)*注意where后的条件，我们使用temp表中的唯一一条记录的id关联KINSHIP_INFO表中的pid*仅当temp第一条记录匹配不到KINSHIP_INFO表中的pid时才会对temp的第二条记录id进行匹配*/SELECT K.ID,K.NAME,temp.ID,temp.username pName FROM temp, KINSHIP_INFO K WHERE temp.ID = K.PID)SELECT * FROM temp

查询出来的最终结果即是期望的结果。如下图所示
在这里插入图片描述

需求2 ：递归找出最小层级所有的父级（子查父）

思路分析：最小层级是孙子，即先从表中所有的grade字段最大的，然后通过这些最小层级递归往上找他们所有的长辈。

		-- 递归找出每一层下面的最小层级(子查父)WITH RECURSIVE  TEMP(ID,USERNAME,PID,childId,childName) AS (-- 查中表中最所有的辈分最小（即grade值最大）的信息，此时查询的信息会存到tem表中SELECT  KI.ID,KI.NAME,KI.PID,KI.ID childId,KI.NAME childName FROM KINSHIP_INFO KI WHERE ki.grade= (SELECT max(grade) as grade FROM KINSHIP_INFO )-- 这里还可以添加过滤条件，比如：我只想找王阿姨孙子的及其所有的长辈，不要隔壁老王相关的人。-- AND KI.ID = 4UNION ALL/*ps：下面这条sql是获取的期望结果中的后两条记录(不包含第一条)*注意where后的条件，我们使用temp表中的唯一一条记录的PID关联KINSHIP_INFO表中的id*仅当temp第一条记录匹配不到KINSHIP_INFO表中的id时才会对temp的第二条记录PID进行匹配*/SELECTK.ID,K.NAME,K.PID,TEMP.childId,TEMP.childName FROMTEMP,KINSHIP_INFO K WHERETEMP.PID = K.ID)SELECT * FROM TEMP

查询的结果如下图所示
在这里插入图片描述

扩展：用RECURSIVE 计算1~100累加后的值

WITH RECURSIVE NUMBERINFO(N) AS ( -- 为结果表,N为字-- 递归开始的第一条记录，只要这个这条语句一执行，就会将第一条记录了的结果存储到NUMBERINFO这种表中，此时可以理解为NUMBERINFO只有一条记录SELECT 1 FROM DUAL UNION ALLSELECT N+1 FROM NUMBERINFO WHERE N < 100    /*这里产生的结果为 2  ，此时NUMBERINFO表的字段n有两条记录分别为1，2* 其他以此类推*				  3*                 ...*                 100*/																				 
)
SELECT SUM(N) FROM NUMBERINFO;