1、查询所有上级
SELECT T0.* FROM ( SELECT @r AS _id, ( SELECT @r := parent_id FROM t_user WHERE id = _id ) AS parent_id1, @l := @l + 1 AS lvl FROM (SELECT @r := 2, @l := 0) vars, -- 这里的2是用户id t_user h WHERE @r <> 0 ) T0 JOIN t_user T1 ON T0._id = T1.id ORDER BY id;
2、查询所有下级
SELECT * FROM ( SELECT @ids AS _ids, ( SELECT @ids := GROUP_CONCAT(id) FROM t_user WHERE FIND_IN_SET(parent_id, @ids) ) AS cids, @l := @l + 1 AS lvl FROM t_user, (SELECT @ids := 1, @l := 0) b -- 这里的1是用户id WHERE @ids IS NOT NULL ) id, t_user DATA WHERE FIND_IN_SET(DATA .id, id._ids) ORDER BY lvl, id
标签:parent,ids,ID,子级,mysql,WHERE,id,SELECT,user From: https://www.cnblogs.com/todarcy/p/16843808.html