根据父id查所有层子id；根据子id查询最上级父id

– 根据父id查所有子id
– SELECT GROUP_CONCAT(id) AS pids
– FROM (
– SELECT * FROM (
– SELECT id,pid
– FROM sys_organize
– ORDER BY pid, id
– ) org_query,
– (SELECT @id := 21) initialisation
– WHERE FIND_IN_SET(pid, @id) > 0
– AND @id := CONCAT(@id, ‘,’, id)
– ) sub_query;

– 根据子id 查所有父级id
– SELECT T2.id
– FROM (
– SELECT
– @r AS _id,
– (SELECT @r := pid FROM sys_organize WHERE id = _id) AS parent_id,
– @l := @l + 1 AS lvl
– FROM
– (SELECT @r := ‘24’, @l := 0) vars,
– sys_organize h
– WHERE @r <> 0) T1
– JOIN sys_organize T2
– ON T1._id = T2.id
– ORDER BY T1.lvl DESC