MySql递归查询父级/子级数据

1.根据父级ID递归查询所有下级ID

SELECT
	id 
FROM
	(
	SELECT
		t1.id,
	IF
		(
			FIND_IN_SET( t1.parent_id, @pids ) > 0,
			@pids := CONCAT( @pids, ',', t1.id ),
			0 
		) AS ischild 
	FROM
		( SELECT id, parent_id FROM table_name t ORDER BY parent_id, id ) t1,
		( SELECT @pids := '16' ) t2 
	) t3 
WHERE
	ischild != 0 
	OR id = '16'

此方法,通过FIND_IN_SET找出当前数据的parent_id是否在@pids的满足条件的字符串当中。如果符合,则将当前数据的id加入@pids；否则isChild设置为0。依次往下查询,最终找出isChild不为0的即为满足条件的数据id集合。

2. 根据子级ID递归查询所有父级ID

SELECT
	t2.id 
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := parent_id FROM table_name WHERE id = _id ) AS parent_id,
		@s := @s + 1 AS sort 
	FROM
		( SELECT @r := 23, @s := 0 ) temp,
		table_name 
	WHERE
		@r > 0 
	) t1
	JOIN table_name t2 ON t1._id = t2.id 
ORDER BY
	t1.sort DESC

补充： sqlite递归查询

<select id="getParentTreeId" parameterType="java.lang.Long" resultType="java.lang.Long">
		with recursive cid(n) AS
        (
            VALUES (#{id})
            UNION
            SELECT parent_id FROM table_name, cid
            WHERE id = cid.n AND delete_dt = 0
            )
        SELECT id  FROM table_name
        WHERE id IN cid
    </select>

    <select id="getChildTreeId" parameterType="java.lang.Long" resultType="java.lang.Long">
		with recursive pid(n) AS
        (
            VALUES (#{id})
            UNION
            SELECT id FROM table_name, pid
            WHERE parent_id = pid.n AND delete_dt = 0
        )
        SELECT id  FROM table_name
        WHERE id IN pid
    </select>