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.查詢出來的結構即為樹狀結構（理論上無窮級數）