程序方式递归 所有父类

----方法执行，采用while特性

List<GmsProductCategory> list = null;
if (Name != null) {
    list = this.list(new LambdaQueryWrapper<GmsProductCategory>().like(GmsProductCategory::getName, Name));
    List<String> collect = list.stream().map(GmsProductCategory::getParentId).collect(Collectors.toList());
    while (collect.size() > 0) {
        List<GmsProductCategory> listData = this.list(new LambdaQueryWrapper<GmsProductCategory>().in(GmsProductCategory::getId, collect));
        collect = listData.stream().map(GmsProductCategory::getParentId).collect(Collectors.toList());
        list.addAll(listData);
    }
} else {
    list = this.list(queryWrapper);
}
return list;

 

mysql执行

 查所有的叶子节点. ( 不含自己 )

SELECT u2.id, u2.name FROM( SELECT @ids AS p_ids, (SELECT @ids := GROUP_CONCAT(id) FROM test_user WHERE FIND_IN_SET(parentId, @ids)) AS c_ids, @l := @l+1 AS LEVEL FROM test_user, (SELECT @ids := '101', @l := 0 ) b #此处为需要传递的父类id. WHERE @ids IS NOT NULL ) u1 JOIN test_user u2 ON FIND_IN_SET(u2.id, u1.p_ids) AND u2.id != '101' #需要包含自己, 则删掉 !=

 

查所有的父节点. ( 含自己 )

SELECT u2.id, u2.name FROM( SELECT @id c_ids, (SELECT @id:=GROUP_CONCAT(parentId) FROM test_user WHERE FIND_IN_SET(id,@id)) p_ids, @l := @l+1 AS LEVEL FROM test_user,(SELECT @id:='105', @l := 0) b WHERE @id IS NOT NULL ) u1 JOIN test_user u2 ON u1.c_ids = u2.id