#递归查询父节点包含当前子节点 不拼接id SELECt T2.需要查询的字段 FROM (SELECt @r AS _id,(SELECT @r := up_id FROM 表名 WHERe id = _id) AS up_id,@l := @l + 1 AS lvl FROM (SELECt @r :=需要查询的id , @l := 0) vars,表名 h WHERe @r <> 0) T1 JOIN 表名 T2 ON T1._id = T2.id and 可加字段筛选条件 ORDER BY T1.lvl 排序方式 #递归查询父节点包含当前子节点 逗号拼接id SELECT GROUP_CONCAT(T2.id SEPARATOR',') FROM (SELECt @r AS _id,(SELECT @r := up_id FROM 表名 WHERe id = _id) AS up_id,@l := @l + 1 AS lvl FROM (SELECt @r :=需要查询的id , @l := 0) vars,表名 h WHERe @r <> 0) T1 JOIN 表名 T2 ON T1._id = T2.id and 可加字段筛选条件 ORDER BY T1.lvl 排序方式父找子
#递归查询子节点包含当前子节点 不拼接id SELECT DATA.需要查询的字段,DATA.需要查询的字段,DATA.需要查询的字段 FROM(SELECt @ids AS _ids,( SELECT @ids := GROUP_CONCAT(id) FROM 表名 WHERe FIND_IN_SET( up_id, @ids ) ) AS cids,@l := @l + 1 AS LEVEL FROM 表名,( SELECt @ids := 需要查询的id, @l := 0 ) b) id,表名 DATA WHERe FIND_IN_SET( DATA.id, ID._ids ) and 可加字段筛选条件 #递归查询子节点包含当前子节点 拼接id SELECT GROUP_CONCAT(DATA.id SEPARATOR',') FROM(SELECt @ids AS _ids,( SELECT @ids := GROUP_CONCAT(id) FROM 表名 WHERe FIND_IN_SET( up_id, @ids ) ) AS cids,@l := @l + 1 AS LEVEL FROM 表名,( SELECt @ids := 需要查询的id, @l := 0 ) b) id,表名 DATA WHERe FIND_IN_SET( DATA.id, ID._ids ) and 可加字段筛选条件 #递归查询子节点包含当前子节点 不拼接id并展示节点等级 SELECT ID.LEVEL,DATA.需要查询的字段,DATA.需要查询的字段,DATA.需要查询的字段 FROM(SELECt @ids AS _ids,( SELECT @ids := GROUP_CONCAT(id) FROM 表名 WHERe FIND_IN_SET( up_id, @ids ) ) AS cids,@l := @l + 1 AS LEVEL FROM 表名,( SELECT @ids := 需要查询的id, @l := 0 ) b) id,表名 DATA WHERe FIND_IN_SET( DATA.id, ID._ids ) and 可加字段筛选条件
实际使用情况
使用时请根据具体表名和排序顺序 进行替换。
