多级目录SQL分层查询

news/2024/9/25 5:23:01/

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

 数据结构:

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>

业务处理 :

java">/*** 是否排除没有数据的分类*/
@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);}
}

返回数据实体类:

java">@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<>();
}


http://www.ppmy.cn/news/1530136.html

相关文章

ubuntu内网穿透后在公网使用ssh登录

需求&#xff1a; 我有一台内网可以通过ssh 22端口访问的设备操作系统是ubuntu server我还有1台拥有公网IP的服务器&#xff0c;IP地址是 6.66.666.6666我想随时从其他网段通过ssh访问我的ubuntu server设备 实现&#xff1a; 工具准备&#xff1a;frp 网址&#xff1a;https…

时间序列LSTM实现

这个代码参考了时间序列预测模型实战案例(三)(LSTM)(Python)(深度学习)时间序列预测(包括运行代码以及代码讲解)_lstm预测模型-CSDN博客 结合我之前所学的lstm-seq2seq里所学习到的知识对其进行预测 import time import numpy as np import pandas as pd import torch import…

【CentOS7】nginx部署前端 gunicorn部署flask后端并使用nginx反向代理

一、前端 将编译好的前端文件放入服务器中/usr/local/project/test_case/dist编辑/usr/local/webserver/nginx/conf/nginx.conf&#xff0c;此为nginx的配置文件 user nginx; worker_processes 2;error_log /logs/nginx/error.log crit;#pid logs/nginx.pid;events …

linux下不同库出现符号冲突的解决方式

在linux下开发时&#xff0c;可能会碰到不同库导出同名函数符号&#xff0c;导致行为异常的问题。 举个例子&#xff1a;我的test.cpp需要链接liba.so以及libb.so&#xff0c;并且两个so库中都有一个同名的函数符号 PrintInt&#xff0c;那在 test.cpp 中我想调用liba.so的Pri…

编译原理3——词法分析

3.1词法分析器的作用 词法分析是编译的第一阶段。词法分析器的主要任务是读入源程序的输入字符、将它们组成词素&#xff0c;生成并输出一个词法单元序列&#xff0c;每个词法单元对应于一个词素。 但在这个过程中&#xff0c;词法分析器还要和语法分析器进行交互。交互&…

无人机黑飞打击技术详解

随着无人机技术的普及&#xff0c;无人机“黑飞”&#xff08;未经授权或违反规定的飞行&#xff09;现象日益严重&#xff0c;对公共安全、隐私保护及重要设施安全构成了严重威胁。为有效应对这一挑战&#xff0c;各国政府和安全机构纷纷研发并部署了一系列无人机黑飞打击技术…

spring boot导入多个配置文件

1、简介 Spring Boot从2.4.x版本开始支持了导入文件的方式来加载配置参数&#xff0c;与spring.config.additional-location不同的是不用提前设置而且支持导入的文件类型相对来说要丰富很多。 我们只需要在application.properties/application.yml配置文件中通过spring.config.…

4.1章节python中顺序结构

顺序结构&#xff08;Sequential Structure&#xff09;是最基本、最简单的程序结构。 顺序结构意味着程序中的语句将按照它们在代码中出现的顺序依次执行。这是大多数编程语言中最直观和自然的执行方式。 在Python中编写顺序结构的程序时&#xff0c;你只需将语句按照你希望它…