无限级树形结构，sql带条件带分页查询

数据库表结构：单表、无限级树，根据pid查找父节点，pid为零则为顶级节点。

需求：根据dict_name模糊查询并分页，分页分的是一级节点。

思路：先查询满足条件的节点的所有顶级节点，并将顶级节点去重分页，在遍历顶级节点递归把每棵树查询出来。

1、数据库建函数用来查询满足条件的顶级节点：getLevelOneId 参数rootId (int(5))

BEGIN
 DECLARE i VARCHAR(100) DEFAULT '';
 DECLARE j VARCHAR(1000) DEFAULT  rootId;

WHILE rootId !=0 DO 
   SET i = (SELECT pid FROM sys_dict WHERE id = rootId);
    IF i !=0  THEN
      set j = i;
      set rootId = i;
        ELSE 
            set rootId = i;
        END IF;  
END WHILE;
return j;
END

2、sql查询去重并分页满足条件的顶级节点

1

SELECT DISTINCT getLevelOneId(id) from sys_dict WHERE `dict_name` LIKE '%123%' limit 1,10

3、遍历顶级id的列表递归画出每个树（mybatis写法，也可以在java里面递归查询）

@Select("select * from sys_dict where pid = #{id} and status <> -1")
    @Results({
            @Result(property="id",column="id"),
            @Result(property="dictCode",column="dict_code"),
            @Result(property="dictName",column="dict_name"),
            @Result(property="parentId",column="pid"),
            @Result(property="createdTime",column="created_time"),
            @Result(property="updatedTime",column="updated_time"),
            @Result(property="children", column="id", javaType=List.class,
                    many=@Many(select="com.towery.mapper.EtrDictMapper.selectTreeById", fetchType= FetchType.EAGER))
    })
    List<TreeNode> selectTreeById(Integer id);

    @Select("select * from sys_dict where status <> -1 and id = #{id}")
    @Results({
            @Result(property="dictCode",column="dict_code"),
            @Result(property="dictName",column="dict_name"),
            @Result(property="createdTime",column="created_time"),
            @Result(property="updatedTime",column="updated_time")
    })
    TreeNode selectById(Integer id);

TreeNode字段

private Integer id;
private Integer parentId;
private List<TreeNode> children = new ArrayList<>();
private String dictName;
private String dictCode;
private Integer type;
private String description;
private Integer leaf;
private Integer sortby;
getter\setter省略

转载自：https://www.cnblogs.com/wscw/p/14176814.html