当前位置 博文首页 > wwwwwwweq的博客：mysql 递归查询 从上往下 从下往上
-- 父查子
select t3.*
from (
select t1.*,
if(find_in_set(t1.父级id, @pids) > 0, @pids := concat(@pids, ',', t1.id),
0) as ischild
from (
select t.*
from t_network_disk_directory t
) t1,
(select @pids := 父级id id ) t2
) t3
where ischild != 0mysql? 8.0?版本
-- 1、 子查询父
WITH recursive tree_cte AS (
SELECT * FROM 表名r WHERE r.子id =#{子id}
UNION ALL
SELECT r.* FROM 表名 r,tree_cte t WHERE t.父id = r.子id
)select * from tree_cte;
-- 2、 父查询子
WITH RECURSIVE tree_cte as
(
select * from 表名 t1 where t1.父id = #{父id}
UNION ALL
select t.* from 表名 t inner join tree_cte tcte on t.父id = tcte.子id
)
SELECT * FROM tree_cte;
-- 包含自己
select * from 表名 t1 where t1.子id = #{父id}
UNION ALL
(
WITH RECURSIVE tree_cte as
(
select * from 表名 t1 where t1.父id = #{父id}
UNION ALL
select t.* from 表名 t inner join tree_cte tcte on t.父id = tcte.子id
)
SELECT * FROM tree_cte)
//对查询的结果进行封装嵌套至实体内
List<Bean> beans= dao.sql();
//根据结果的父级id 进行分组
Map<Long, List<Bean>> collect = beans.stream().filter(f -> null != f.父级id()).collect(Collectors.groupingBy(bean::get父级id));
//分组后的数据 每一个list<Bean> value 都是 long key 子集 for 循环结束的后的 数据 即为树形结构
for (Bean bean: Beans) {
if (collect.get(bean.getId()) != null) {
bean.setChildren(collect.get(bean.getId()));
}
}