mysql递归查询CTE

CTE是Mysql8.0开始支持

这里是一个例子表，此处示例需要拿到 ID 为 102 下的所有子集

这里是sql语句

WITH RECURSIVE cte AS (
SELECT
	ID,
	PID,
	NAME,
	LEVEL,
	Type 
FROM
	tmp_zjs 
WHERE
	ID = '102' 
UNION ALL
SELECT
	sou.ID,
	sou.PID,
	sou.NAME,
	sou.LEVEL,
	sou.Type 
FROM
	cte c
	INNER JOIN tmp_zjs sou ON c.ID = sou.PID 
) 
SELECT * FROM	cte

得到的结果是