mysql查询父/子节点

mysql查询父节点：

SELECT t2.id, t2.parent_id 
	FROM(
		SELECT @r AS _id,
			(SELECT @r := parent_id FROM department WHERE id = _id) AS parent_id,
			@a := @a + 1 AS tmp
		FROM 
			(SELECT @r := '查询的子级id', @a := 0) vars,
			department h  -- 不可少
		WHERE @r <> 0) t1
	JOIN department t2
	ON t1._id = t2.id
ORDER BY id ASC;
-- 或者：	ORDER BY t1.tmp DESC;

mysql查询子节点：

SELECT id FROM(
		SELECT t1.id,
			IF(FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', id), 0) AS ischild
		FROM(SELECT id, parent_id FROM department t WHERE t.status = 1 ORDER BY parent_id, id) t1,
		(SELECT @pids := '要查的父级id') t2
	) t3 WHERE ischild != 0;

结合GROUP_CONCAT()函数使用

SELECT GROUP_CONCAT(d.id SEPARATOR ':') tmp_ids FROM (
SELECT t2.id, t2.parent_id 
	FROM(
		SELECT @r AS _id,
			(SELECT @r := parent_id FROM department WHERE id = _id) AS parent_id,
			@a := @a + 1 AS tmp
		FROM 
			(SELECT @r := '查询的子级id', @a := 0) vars,
			department h  -- 不可少
		WHERE @r <> 0) t1
	JOIN department t2
	ON t1._id = t2.id
ORDER BY id ASC
) d

说明：GROUP_CONCAT(d.id SEPARATOR ‘:’) 将查询到的id，用 : 连接（1:2:3）

mysql8父子查询：

使用 WITH RECURSIVE

表

父查子:

WITH RECURSIVE temp AS  ( -- 将结果表命名为temp
	SELECT * FROM parent_test tp WHERE tp.name = '张' -- 查询出父id这条记录，此时这条记录已存在temp表中
	UNION All
	SELECT tp.* FROM parent_test tp,temp t WHERE t.id = tp.parent_id
)select * from temp

子查父:

WITH RECURSIVE temp AS  ( -- 将结果表命名为temp
	SELECT * FROM parent_test tp WHERE tp.name = '张三二二' -- 查询出父id这条记录，此时这条记录已存在temp表中
	UNION All
	SELECT tp.* FROM parent_test tp,temp t WHERE t.parent_id = tp.id
)select * from temp  -- 最后的结果在临时表temp