mysql如何实现树状结构数据列表

1.数据库设计

CREATE TABLE `expense_type` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '费用类型唯一主键ID',
  `NAME` varchar(100) DEFAULT NULL COMMENT '费用类型名称',
  `PID` int(11) DEFAULT NULL,
  `IS_DEL` varchar(20) DEFAULT '0' COMMENT '0为未删除，1为已删除，默认为0',
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `isDel` (`IS_DEL`) USING BTREE COMMENT '假删除字段索引'
) ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

2.service层代码

@Override
    public RestResponse getAllExpenseType() {
        List<ExpenseType> expenseTypes = expenseTypeMapper.selectList();//查询此表的所有数据sql即可
        expenseTypes = generateTree(expenseTypes);
        return RestResponse.success(expenseTypes);
    }

   
    private List<ExpenseType> generateTree(List<ExpenseType> expenseTypeList) {
        List<ExpenseType> orgList = new ArrayList<>();
        for (ExpenseType sysMenu : expenseTypeList) {
            if (sysMenu.getPid() == null) {
                orgList.add(sysMenu);
                addSubs(sysMenu, expenseTypeList);
            }
        }
        return orgList;
    }

    private ExpenseType addSubs(ExpenseType org, List<ExpenseType> list) {
        for (ExpenseType sysOrg : list) {
            if (org.getId().equals(sysOrg.getPid())) {
                org.getSubs().add(addSubs(sysOrg, list));
            }
        }
        return org;
    }

3.查询出来的结构即为树状结构（理论上无穷级数）