MySQL 递归SQL

在开发中我们经常会有树形表的设计，需要查询某个节点数据的父节点和子节点，在代码中使用递归函数做查询也可以实现，其实sql语句也可以实现这样的需求，不用存储过程也可以。

递归查询制定id子节点

WITH RECURSIVE tmp0 AS (
	SELECT
		a.* 
	FROM
		admin_organization a 
	WHERE
		a.id = #{id}
	UNION
	SELECT
		t1.* 
	FROM
		admin_organization t1,
		tmp0 t0 
	WHERE
		1 = 1 
		AND t1.parent_id = t0.id 
	) SELECT
	a.id,
	a.parent_id,
	a.LEVEL levelValue,
	( SELECT `means` FROM system_data_reference WHERE param = 'org_level' AND VALUE = LEVEL ) levelMeans,
	a.full_name fullName,
	a.short_name shortName,
	a.sort 
FROM
	tmp0 a

递归查询父节点

WITH RECURSIVE tmp0 AS (
	SELECT
		a.* 
	FROM
		extend_info_snap a 
	WHERE
		a.id = '03bbdbe8-5a4c-450f-abc1-9e853b7f3e49' UNION
	SELECT
		t1.* 
	FROM
		extend_info_snap t1,
		tmp0 t0 
	WHERE
		1 = 1 
		AND t1.id = t0.parent_id 
	) SELECT
	a.id,
	a.CODE 
FROM
	tmp0 a