mysql 部门查询 查询所有子级,父级id

这里提供2个个人觉得比较常用的sql

一、 查询所有子级（不包含本身）

@Select({"<script>",
            "select id from (" +
                    " select t1.id," +
                    " if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild" +
                    " from (" +
                    " select id,parent_id from c_core_org t order by parent_id, id" +
                    " ) t1," +
                    " (select @pids := #{orgId}) t2" +
                    " ) t3 where ischild != 0",
            "</script>"})

 

二、查询所有父级（包含本身）

5.6版本

"SELECT T1._id groupID" +
                    " FROM (" +
                    " SELECT" +
                    " @r AS _id," +
                    " (SELECT @r := parent_id FROM c_core_org WHERE id = _id) AS parent_id," +
                    " @l := @l + 1 AS lvl FROM (SELECT @r := #{orgId}, @l := 0) vars," +
                    " c_core_org h WHERE @r != '0'" +
                    " ) T1",

 8.0

"with recursive t as " +
                "( " +
                "select * from c_core_org where id = #{orgId} " +
                "union all " +
                "select a.* from c_core_org a join t on a.id = t.parent_id " +
                ") " +
                "select id from t"

　　

 

以上2个查询id为我的主键 , c_core_org 是我的表名,parent_id 是我的父id字段

 

分享到此结束，

感谢观看

如有错误或不足麻烦大佬提出，我及时改正

谢谢!

2021-09-26 15:46:50