MySql递归RECURSIVE的详解

背景：

在实际开发的过程中，我们会遇到一些数据是层级关系的、要展示数据子父级关系的时候，
第一个解决方案：将数据库中的所有数据都查询出来用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 = 4
	UNION ALL
	/*ps：下面这条sql是获取的期望结果中的后两条记录(不包含第一条)
	*注意where后的条件，我们使用temp表中的唯一一条记录的PID关联KINSHIP_INFO表中的id
	*仅当temp第一条记录匹配不到KINSHIP_INFO表中的id时才会对temp的第二条记录PID进行匹配
	*/
	SELECT
		K.ID,
		K.NAME,
		K.PID,
		TEMP.childId,
		TEMP.childName 
	FROM
		TEMP,
		KINSHIP_INFO K 
	WHERE
		TEMP.PID = K.ID
	
	)
	SELECT * FROM TEMP

查询的结果如下图所示

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

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

最终计算的结果为：