mysql根据父节点递归查询所有子节点

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排序

SELECT b.id,b.parent_id,b.name
FROM(
    SELECT @ids AS _ids,
    (SELECT @ids := GROUP_CONCAT(parent_id) FROM sys_dept WHERE FIND_IN_SET(id, @ids)) AS cids
    FROM sys_dept,(SELECT @ids := #{id}) b 
    WHERE @ids IS NOT NULL 
) a,sys_dept b 
WHERE FIND_IN_SET(b.id, a._ids) or FIND_IN_SET(b.id,a.cids)
ORDER BY id

使用时候把 sys_dept  改成自己表