mysql 树形结构查询（向上查询、向下查询）

树形向上查询（前提：Mysql8.0才能用）

WITH RECURSIVE temp as (
    SELECT t.* FROM sys_depart t WHERE t.id = #{id}
    UNION ALL
    SELECT t.* FROM sys_depart t INNER JOIN temp ON t.id = temp.parent_id
)
SELECT * FROM temp WHERE org_type = '2'

 树形向上查询（版本不限制）

SELECT
    node.id,
    node.parent_id,
    node.title,
    path.depth 
FROM
    (
    SELECT
        @r AS _id,
        ( SELECT @r := parent_id FROM water_depart WHERE id = _id ) AS parent_id,
        @l := @l + 1 AS depth 
    FROM
        ( SELECT @r := #{id}, @l := 0 ) vars,
        water_depart h 
    WHERE
        @r != 0 
    ) path,
    water_depart node 
WHERE
    node.id = path._id

树形向下查询（前提：Mysql8.0才能用）

WITH RECURSIVE tree AS (
    SELECT id, parent_id, title, level FROM water_depart WHERE id = #{id}
    UNION ALL
    SELECT d.id, d.parent_id, d.title, d.level FROM water_depart d
    INNER JOIN tree t ON t.id = d.parent_id
)
SELECT * FROM tree;