【mysql 树形结构表，查询所有结点sql样例】

mysql 树形结构表，查询所有结点sql

**查selId的所有父结点**
-- 输入值： 
SELECT t2.id, t2.org_name
FROM (	SELECT
             @r AS _id,
             (SELECT @r := parent_org_id FROM t_cashier_org WHERE id = _id) AS parent_id,
             @l := @l + 1 AS lvl
         FROM
             (SELECT @r := #{selId}, @l := 0) vars,  -- 查询id为21的所有上级
             t_cashier_org h
WHERE @r <> 0) t1
JOIN t_cashier_org t2 ON t1._id = t2.id and T2.id not in (#{selId}) -- 不包含输入节点
ORDER BY t1.lvl DESC;

查selId的所有子结点

select id,t3.org_name from (
   select t1.id,t1.org_name,if(find_in_set(parent_org_id,@pids) >0,@pids := concat(@pids,',', id),0) as ischild
   from (select * from t_cashier_org t where t.status =1 order by parent_org_id, id) t1,
        (select@pids := 20 ) t2 -- (20)要查询的菜单节点 id
) t3 where ischild !=0;