捕获.PNG

子查父 （根据子节点查询所有父节点）

SELECT
    T2.*
FROM
    (
        SELECT
            @r AS _id,
            (
                SELECT
                    @r := parent_id
                FROM
                    t_sys_organization
                WHERE
                    id = _id
            ) AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := '4', @l := 0) vars,
            t_sys_organization h
        WHERE
            @r <> 0
    ) T1
JOIN t_sys_organization T2 ON T1._id = T2.id
ORDER BY
    T1.lvl DESC

父查子 （根据节点查询所有字点）

  SELECT 
    ID.level,
    DATA.* 
  FROM
    (SELECT 
      @ids AS _ids,
      (SELECT 
        @ids := GROUP_CONCAT(id) 
      FROM
        t_sys_organization 
      WHERE FIND_IN_SET(parent_id, @ids)) AS cids,
      @l := @l + 1 AS LEVEL 
    FROM
      t_sys_organization,
      (SELECT 
        @ids := '1',
        @l := 0) b 
    WHERE @ids IS NOT NULL) id,
    t_sys_organization DATA 
  WHERE FIND_IN_SET(DATA.id, ID._ids) 
  ORDER BY LEVEL,
    id ;

查询结果

捕获.PNG

父查子（方法二）

SELECT 
  O2.* 
FROM
  (SELECT 
    @r AS _id,
    (SELECT 
      @r := id 
    FROM
      t_sys_organization 
    WHERE parent_id = _id) AS id,
    @l := @l + 1 AS lvl 
  FROM
    (SELECT 
      @r := '1',
      @l := 0) vars,
    t_sys_organization h) O1 
  JOIN t_sys_organization O2 
    ON O1._id = O2.parent_id 
WHERE O2.enabled = 1 
ORDER BY O1.lvl