SpringBoot获取树状结构数据-SQL处理

前言

在开发中，层级数据（树状结构）的获取往往可能是我们一大难点，我现在将自己获取的树状结构数据方法总结如下,希望能给有需要的小伙伴有所帮助！

一、测试数据准备

/*
 Navicat Premium Data Transfer

 Source Server         : 本地MySQL-local
 Source Server Type    : MySQL
 Source Server Version : 80100
 Source Host           : localhost:33306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80100
 File Encoding         : 65001

 Date: 06/09/2023 11:21:45
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for region
-- ----------------------------
DROP TABLE IF EXISTS `region`;
CREATE TABLE `region`  (
  `id` bigint(0) NOT NULL COMMENT '主键id',
  `region_id` bigint(0) NULL DEFAULT NULL COMMENT '区域id',
  `region_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '区域编码',
  `region_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '区域名称',
  `parent_id` bigint(0) NULL DEFAULT NULL COMMENT '父节点id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `region_id`(`region_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '地区信息' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of region
-- ----------------------------
INSERT INTO `region` VALUES (1, 10001, 'CODEA0001', '中国', 0);
INSERT INTO `region` VALUES (2, 10002, 'CODEB0001', '安徽省', 10001);
INSERT INTO `region` VALUES (3, 10003, 'CODEB0002', '黑龙江省', 10001);
INSERT INTO `region` VALUES (4, 10004, 'CODEB0003', '广东省', 10001);
INSERT INTO `region` VALUES (5, 10005, 'CODEC0001', '合肥市', 10002);
INSERT INTO `region` VALUES (6, 10006, 'CODEC0002', '淮北市', 10002);
INSERT INTO `region` VALUES (7, 10007, 'CODEC0003', '哈尔滨市', 10003);
INSERT INTO `region` VALUES (8, 10008, 'CODEC0004', '鹤岗市', 10003);
INSERT INTO `region` VALUES (9, 10009, 'CODEC0005', '广州市', 10004);
INSERT INTO `region` VALUES (10, 10010, 'CODEC0006', '深圳市', 10004);
INSERT INTO `region` VALUES (11, 10011, 'CODED0001', '龙华区', 10010);
INSERT INTO `region` VALUES (12, 10012, 'CODED0002', '南山区', 10010);
INSERT INTO `region` VALUES (13, 10013, 'CODED0003', '天河区', 10009);

SET FOREIGN_KEY_CHECKS = 1;