SELECT t3.* FROM(SELECT t1.*, IF (FIND_IN_SET(parent_id, @pids) > 0,@pids := CONCAT(@pids, ',', id),'0') AS ischild FROM(SELECT t.id,t.parent_id,t.NAME FROM t_parent AS t ORDER BY t.id ASC) t1, (SELECT @pids := '0') t2) t3 WHERE ischild != '0'
使用时候把 t_parent 改成自己表,@pids := '0’ 这个是要查询的父id号(不包含自身)
SELECT rd.* FROM (SELECT * FROM sys_dict WHERE parent_id IS NOT NULL) rd, (SELECT @pid := '1') pd WHERE FIND_IN_SET(parent_id, @pid) > 0 AND @pid := concat(@pid, ',', id) union select * from sys_dict where id = @pid;
使用时候把 sys_dict 改成自己表,@pid := '1' 这个是要查询的父id号(包含自身)
如果不想查询结果包含自身,去掉后边的union
通过子id查询父id到根节点
SELECT t3.* FROM(SELECT t1.*, IF (FIND_IN_SET(id, @ids) > 0,@ids := CONCAT( parent_id,',', @ids),'0') AS isparent FROM(SELECT t.id,t.parent_id,t.NAME FROM t_parent AS t ORDER BY t.id DESC) t1, (SELECT @ids := '111') t2) t3 WHERE t3.isparent != '0'
使用时候把t_parent 改成自己表,@ids := '111’这个是要查询的子id号,还有和查询父id不同的地方是ORDER BY t.id DESC,这里使用desc排序
标签:递归,parent,pid,ids,节点,mysql,查询,id,SELECT From: https://www.cnblogs.com/huang2979127746/p/18054041