MYSQL 查找所有的父级或子级（多级）

举例t_user 表是父子级关系， 有字段id 和 字段parent_id



查找父级例子
sql语句 查找所有父级（包含本身）


SELECT T2.t_id
FROM (
    SELECT
    @r AS _id,
    (SELECT @r := parent_id FROM t_user WHERE t_id = _id) AS parent_id,
    @l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars,
      t_user h WHERE @r <> 0 AND parent_id > 0
  ) T1 JOIN t_user T2 ON T1._id = T2.t_id ORDER BY T1.lvl DESC

上面5是传参数id



查找子级例子
第一种方式：
SELECT  DATA.id  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 level
            FROM t_user,
            (SELECT @ids :=#{id}, @l := 0 ) b
       
        ) id, t_user DATA
        WHERE FIND_IN_SET(DATA.id, ID._ids)
        ORDER BY  id
上面#{id}是传参数id



第二种方式（可能有点错误）：
sql语句 查找所有子级（不包含本身）
SELECT t_id FROM
(
    SELECT * FROM t_user where parent_id > 0 ORDER BY parent_id, t_id DESC
  ) realname_sorted,
  (SELECT @pv :=1) initialisation
  WHERE (FIND_IN_SET(parent_id,@pv)>0 And @pv := concat(@pv, ',', t_id))
上面1是传参数id