Mysql刷题笔记 12.25 递归创建路径

Mysql练习

-- 12.25 递归创建路径
T1225
+------+------+
| ID   | PID  |
+------+------+
|    1 |    0 |
|    2 |    1 |
|    3 |    2 |
|    4 |    3 |
+------+------+
结果
+------+------+------------+
| ID   | PID  | PATH       |
+------+------+------------+
|    1 |    0 | 1          |
|    2 |    1 | 1->2       |
|    3 |    2 | 1->2->3    |
|    4 |    3 | 1->2->3->4 |
+------+------+------------+

DROP FUNCTION IF EXISTS f1225;
DELIMITER //  -- 修改结束符

CREATE FUNCTION f1225(Id INT)
RETURNS VARCHAR(4000)
BEGIN
	DECLARE sTemp VARCHAR(4000);
	DECLARE idTemp INTEGER;
	SET sTemp='1';
	SET idTemp = 1;
	WHILE idTemp < Id DO
		SET sTemp= CONCAT(STemp,'->',cast((idTemp+1) as CHAR));
		SET idTemp = idTemp +1;
	END WHILE;
	RETURN sTemp;
END
//
DELIMITER ;

SELECT ID,PID,f1225(ID) AS PATH FROM T1225;


WITH RECURSIVE TEST_CTE AS
(
	SELECT t.ID,t.PID,Cast(ID AS CHAR) AS PATH
  FROM T1225 t
  WHERE t.ID=1
  UNION ALL
  SELECT p.ID,p.PID,CONCAT(CTE.PATH,"->",Cast(p.ID AS CHAR)) AS PATH
  FROM T1225 p
  INNER JOIN TEST_CTE CTE ON p.PID = CTE.ID
)
SELECT * FROM TEST_CTE