多级目录SQL分层查询

需求：有多级目录，而目录的层级是不固定的，如下图所示：

 数据结构：

sql语句：

<select id="getList" resultType="com.hikvision.idatafusion.dhidata.bean.vo.knowledgebase.KnowledgeBaseTypeTreeVo">with recursive tree as(select distinct kbt.id, kbt.type_name, kbt.type_sort, kbt.parent_id, 1 as type_level,kbt.id::text as id_path, kbt.type_name::text as type_name_path, kbt.type_sort::text as type_sort_pathfrom knowledge_base_type kbt<if test="excludeNoDataType">join knowledge_base kb on kb.delete_flag = 0 and kb.type_id_list <![CDATA[&&]]> array[kbt.id]::int8[]and kb.publish_state = '1'</if>where kbt.delete_flag = 0unionselect tree.id, tree.type_name, tree.type_sort, parent.parent_id, tree.type_level + 1 as type_level,concat(parent.id, '/', tree.id_path) as id_path,concat(parent.type_name, '/', tree.type_name_path) as type_name_path,concat(parent.type_sort, '/', tree.type_sort_path) as type_sort_pathfrom treejoin knowledge_base_type parent on parent.delete_flag = 0 and parent.id = tree.parent_id)select id, type_name, type_sort, type_level, id_path, type_name_path, type_sort_pathfrom (select *,  ROW_NUMBER() OVER (PARTITION BY id ORDER BY type_level desc) AS rn from tree) twhere t.rn = 1
</select>

业务处理 ：

/*** 是否排除没有数据的分类*/
@Override
public List<KnowledgeBaseTypeTreeVo> getList(boolean excludeNoDataType) {KnowledgeBaseTypeQueryDto knowledgeBaseTypeQueryDto = new KnowledgeBaseTypeQueryDto();knowledgeBaseTypeQueryDto.setExcludeNoDataType(excludeNoDataType);List<KnowledgeBaseTypeTreeVo> knowledgeBaseTypeTreeVoList  = knowledgeBaseTypeDao.getList(knowledgeBaseTypeQueryDto);// 对分类进行分层、排序Map<Long, KnowledgeBaseTypeTreeVo> firstLevelMap = new HashMap<>(); // 一级分类for (KnowledgeBaseTypeTreeVo knowledgeBaseType : knowledgeBaseTypeTreeVoList) {String[] idPath = knowledgeBaseType.getIdPath().split("/");// 生成一级分类long firstLevelId = Long.parseLong(idPath[0]);if(!firstLevelMap.containsKey(firstLevelId)) {KnowledgeBaseTypeTreeVo firstLevel = new KnowledgeBaseTypeTreeVo();firstLevel.setId(firstLevelId);firstLevel.setTypeName(knowledgeBaseType.getTypeNamePath().split("/")[0]);firstLevel.setTypeSort(Integer.parseInt(knowledgeBaseType.getTypeSortPath().split("/")[0]));firstLevel.setParentId(knowledgeBaseType.getParentId());firstLevelMap.put(firstLevelId, firstLevel);}// 遍历生成二级、三级...分类KnowledgeBaseTypeTreeVo firstLevel = firstLevelMap.get(firstLevelId); // 一级Map<Long, KnowledgeBaseTypeTreeVo> childrenMap = firstLevel.getChildrenMap();for (int i = 1; i < idPath.length; i++) { // 从二级开始遍历long typeId = Integer.parseInt(idPath[i]);if(!childrenMap.containsKey(typeId)) {KnowledgeBaseTypeTreeVo treeVo = new KnowledgeBaseTypeTreeVo();treeVo.setId(typeId);treeVo.setTypeName(knowledgeBaseType.getTypeNamePath().split("/")[i]);treeVo.setTypeSort(Integer.parseInt(knowledgeBaseType.getTypeSortPath().split("/")[i]));treeVo.setParentId((long) Integer.parseInt(idPath[i-1]));childrenMap.put(typeId, treeVo);}childrenMap = childrenMap.get(typeId).getChildrenMap(); // 下一级}// 排序for(KnowledgeBaseTypeTreeVo firstKnowledgeBaseType : firstLevelMap.values()) {this.sortChildren(firstKnowledgeBaseType);}knowledgeBaseType.setChildrenMap(firstLevelMap);}// 结果处理后返回List<KnowledgeBaseTypeTreeVo> knowledgeBaseTypeTreeList = firstLevelMap.values().stream().collect(Collectors.toList());return knowledgeBaseTypeTreeList;
}/*
* 分类按TypeSort倒序
* */
private void sortChildren(KnowledgeBaseTypeTreeVo treeVo) {if(MapUtils.isEmpty(treeVo.getChildrenMap())) {return;}treeVo.setChildren(treeVo.getChildrenMap().values().stream().sorted(Comparator.comparingInt(KnowledgeBaseTypeTreeVo::getTypeSort).reversed()).collect(Collectors.toList()));for(KnowledgeBaseTypeTreeVo child: treeVo.getChildrenMap().values()) {sortChildren(child);}
}

返回数据实体类：

@Data
@ToString
@EqualsAndHashCode(callSuper = false)
public class KnowledgeBaseTypeTreeVo implements Serializable {private static final long serialVersionUID = 1L;@ApiModelProperty(value = "ID")private Long id;@ApiModelProperty(value = "类型名称")private String typeName;@ApiModelProperty(value = "序号，排序")private Integer typeSort;@ApiModelProperty(value = "父节点ID")public Long parentId;@ApiModelProperty(value = "分类层级")private Integer typeLevel;@ApiModelProperty(value = "分类ID层级路径")private String idPath;@ApiModelProperty(value = "分类名称层级路径")private String typeNamePath;@ApiModelProperty(value = "分类排序层级路径")private String typeSortPath;@ApiModelProperty(value = "子分类")private List<KnowledgeBaseTypeTreeVo> children = new ArrayList();@ApiModelProperty(value = "分类")private Map<Long, KnowledgeBaseTypeTreeVo> childrenMap = new HashMap<>();
}