mysql递归查询所有父级或子级字段
一. 递归查询所有父级id
SELECT t1._id
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM tb_table WHERE sub_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM (SELECT @r := '子id条件', @l := 0') vars,
tb_table t WHERE @r != '0'
) t1
二. 递归查询父级的其它字段
SELECT GROUP_CONCAT(t1.parent_name) mergerName
FROM (
SELECT
@r AS _id,
(SELECT @s := parent_name FROM tb_table WHERE sub_id = _id) AS parent_name,
(SELECT @r := parent_id FROM tb_table WHERE sub_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM (SELECT @r := '子id条件', @l := 0, @s := '') vars,
tb_table t WHERE @r != '0'
) t1
三.递归查询所有子级id
SELECT t3.sub_id
FROM (
SELECT
t1.sub_id,
IF(FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', sub_id), 0) AS ischild
FROM
(SELECT sub_id,parent_id FROM tb_table t ORDER BY parent_id, sub_id) t1,
(SELECT @pids := '父id条件') t2
) t3
WHERE ischild != 0
四. 递归查询子级的其他字段
SELECT t3.sub_id, t3.sub_name
FROM (
SELECT
t1.sub_id,
t1.sub_name,
IF(FIND_IN_SET(parent_id, @pids) > 0, @pids := CONCAT(@pids, ',', sub_id), 0) AS ischild
FROM
(SELECT sub_id, parent_id, sub_name FROM tb_table t ORDER BY parent_id, sub_id) t1,
(SELECT @pids := '父id条件') t2
) t3
WHERE ischild != 0
sub_id和parent_id分别是子id和父id, tb_table是表名, 其他字段自定义