SQL递归查询树型分类数据

比眉伴天荒 2024-03-25 20:29 73阅读 0赞

> **目录**
> 
> 前言
> 
> 1.准备分类数据
> 
>  2.递归原理
> 
> 3.实现
> 
>  4.结合mybatis查询
> 
> 总结

--------------------

## 前言 ##

相信大家在处理业务的时候经常会遇到分类数据，当面对这种情况时该如何处理呢？在这里我使用了两种方式解决：一种使用**sql递归的方式**，另一种是**java代码方式**处理（下一期）。

--------------------

**一、SQL递归**

### 1.准备分类数据 ###

代码如下（示例）：

DROP TABLE IF EXISTS `course_category`;
    CREATE TABLE `course_category`  (
      `id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
      `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '分类名称',
      `label` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分类标签默认和名称一样',
      `parentid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '父结点id（第一级的父节点是0，自关联字段id）',
      `is_show` tinyint NULL DEFAULT NULL COMMENT '是否显示',
      `orderby` int NULL DEFAULT NULL COMMENT '排序字段',
      `is_leaf` tinyint NULL DEFAULT NULL COMMENT '是否叶子',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '课程分类' ROW_FORMAT = DYNAMIC;
    
    -- ----------------------------
    -- Records of course_category
    -- ----------------------------
    
    INSERT INTO `course_category` VALUES ('1-3-3', '.NET', '.NET', '1-3', 1, 3, 1);
    INSERT INTO `course_category` VALUES ('1-3-4', 'Objective-C', 'Objective-C', '1-3', 1, 4, 1);
    INSERT INTO `course_category` VALUES ('1-3-5', 'Go语言', 'Go语言', '1-3', 1, 5, 1);
    
    INSERT INTO `course_category` VALUES ('1-4', '数据库', '数据库', '1', 1, 4, 0);
    INSERT INTO `course_category` VALUES ('1-4-1', 'Oracle', 'Oracle', '1-4', 1, 1, 1);
    INSERT INTO `course_category` VALUES ('1-4-2', 'MySQL', 'MySQL', '1-4', 1, 2, 1);
    INSERT INTO `course_category` VALUES ('1-4-3', 'SQL Server', 'SQL Server', '1-4', 1, 3, 1);
    INSERT INTO `course_category` VALUES ('1-4-4', 'DB2', 'DB2', '1-4', 1, 4, 1);
    INSERT INTO `course_category` VALUES ('1-4-5', 'NoSQL', 'NoSQL', '1-4', 1, 5, 1);
    INSERT INTO `course_category` VALUES ('1', '根结点', '根结点', '0', 1, 1, 0);
    INSERT INTO `course_category` VALUES ('1-1', '前端开发', '前端开发', '1', 1, 1, 0);
    INSERT INTO `course_category` VALUES ('1-1-1', 'HTML/CSS', 'HTML/CSS', '1-1', 1, 1, 1);
    INSERT INTO `course_category` VALUES ('1-1-2', 'JavaScript', 'JavaScript', '1-1', 1, 2, 1);
    INSERT INTO `course_category` VALUES ('1-1-3', 'jQuery', 'jQuery', '1-1', 1, 3, 1);
    INSERT INTO `course_category` VALUES ('1-1-4', 'ExtJS', 'ExtJS', '1-1', 1, 4, 1);
    INSERT INTO `course_category` VALUES ('1-1-5', 'AngularJS', 'AngularJS', '1-1', 1, 5, 1);
    
    INSERT INTO `course_category` VALUES ('1-2', '移动开发', '移动开发', '1', 1, 2, 0);
    INSERT INTO `course_category` VALUES ('1-2-1', '微信开发', '微信开发', '1-2', 1, 1, 1);
    INSERT INTO `course_category` VALUES ('1-2-2', 'iOS', 'iOS', '1-2', 1, 2, 1);
    INSERT INTO `course_category` VALUES ('1-2-3', '手游开发', '手游开发', '1-2', 1, 3, 1);
    INSERT INTO `course_category` VALUES ('1-2-4', 'Swift', 'Swift', '1-2', 1, 4, 1);
    INSERT INTO `course_category` VALUES ('1-2-5', 'Android', 'Android', '1-2', 1, 5, 1);
    
    INSERT INTO `course_category` VALUES ('1-3', '编程开发', '编程开发', '1', 1, 3, 0);
    INSERT INTO `course_category` VALUES ('1-3-1', 'C/C++', 'C/C++', '1-3', 1, 1, 1);
    INSERT INTO `course_category` VALUES ('1-3-2', 'Java', 'Java', '1-3', 1, 2, 1);

**`表图如下所示：`**

![8ba2b711e37e4ea1bdd1949b00ed6835.png][]

### 2.递归原理 ###

要实现sql递归查询，首先要明白其原理。

**递归查询原理**：SQL Server中的递归查询是通过CTE(表表达式)来实现。至少包含两个子查询，第一个查询为定点成员（种子查询），种子查询只是作为一个根查询，用于递归的定位；第二个查询被称为递归查询， 这两个子查询可以通过 [UNION][]、UNION ALL或UNION DISTINCT 连接在一起。

**注意**：**RECURSIVE 关键字只在MySQL8+版本生效。**种子查询只会执行一次，并得到初始的数据作为根子集，而递归查询没有显式的递归终止条件，只有当第二个递归查询重复执行（实现递归）直到没有新的行产生，返回空结果集或是超出了递归次数的最大限制时才停止递归。最终将所有的结果集都查询出来，这对于深层查询（如具有父子关系的查询）是非常有用的。

**优点：**效率高，大量数据集下，速度比程序的查询快。

**SQL递归的常见形式：**

> WITH RECURSIVE CTE AS (
> 
> SELECT column1,column2... FROM tablename WHERE conditions
> 
> UNION ALL
> 
> SELECT column1,column2... FROM tablename
> 
> INNER JOIN CTE ON conditions
> 
> )

### 3.实现 ###

sql如下：``

with recursive t1 as (
    select * from course_category p where id= "1"
        union all
        select t.* from course_category t inner join t1 on t1.id = t.parentid
        )
        select * from t1 order by t1.id, t1.orderby

查询结果如下：

![9e0191c3dda645f38543d02434f4b7d1.png][]

### 4.结合mybatis查询 ###

引入依赖如下：

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.3.12.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.example</groupId>
        <artifactId>testdemo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>testdemo</name>
        <description>testdemo</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.1</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
        </dependencies>
    
        <build>
            <resources>
                <resource>
                    
                    <directory>src/main/java</directory>
                    <includes>
                        <include>**/*.xml</include>
                    </includes>
                </resource>
            </resources>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.1</version>
                    <configuration>
                        <source>1.8</source>
                        <target>1.8</target>
                        <encoding>UTF-8</encoding>
                    </configuration>
                </plugin>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

yml配置：

>     spring:
>       datasource:
>         username: root
>         password: root
>         url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
>         driver-class-name: com.mysql.cj.jdbc.Driver
> 
>     #  注意这里的配置 我是把mapper映射放在java目录下 才这样写的
>     mybatis-plus:
>       mapper-locations: com/example/testdemo/dao/*.xml

目录结构如下所示：

![18cf143b1b7e4732a53a3b8aea170cce.png][]

Course实体类：

@Data
    @TableName(value = "course_category")
    public class Course implements Serializable {
    
        @TableField(value = "id")
        private String id;
    
        private String name;
    
        private String label;
    
        @TableField(value = "parentid")
        private String parentId;
    
        @TableField(value = "is_show")
        private Integer isShow;
    
        private Integer orderby;
    
        @TableField(value = "is_leaf")
        private Integer isLeaf;
    
        @TableField(exist = false)
        List<Course> courseList;
    }

CourseMapper映射类：

@Mapper
    public interface CourseMapper extends BaseMapper<Course> {
    
        List<Course> selectTreeByRootId(String id);
    }

CourseMapper.xml：

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.example.testdemo.mapper.CourseMapper">
    
        <select id="selectTreeByRootId" resultType="com.example.testdemo.entity.Course" parameterType="string">
    
            with recursive t1 as (
                select * from course_category p where id= #{id}
        union all
        select t.* from course_category t inner join t1 on t1.id = t.parentid
        )
        select * from t1 order by t1.id, t1.orderby
        </select>
    </mapper>

因为返回数据并不满足分类格式，所以 最后再把数据封装进Crouse就行 ，封装操作如下：

CourseServiceImpl类：

@Autowired
        CourseMapper courseMapper;
        @Override
        public  List<Course> selectTreeNode(String id){
            List<Course> courseList =  courseMapper.selectTreeByRootId(id);
            List<Course> categoryTreeDtos = new ArrayList<>();
            HashMap<String, Course> mapTemp = new HashMap<>();
            courseList.stream().forEach(item->{
                mapTemp.put(item.getId(),item);
                //只将根节点的下级节点放入list
                if(item.getParentId().equals("1")){
                    categoryTreeDtos.add(item);
                }
                Course fatherCourse = mapTemp.get(item.getParentId());
                if(fatherCourse!=null){
                    if(fatherCourse.getCourseList() ==null){
                        fatherCourse.setCourseList(new ArrayList<Course>());
                    }
                    //向节点的下级节点list加入节点
                    fatherCourse.getCourseList().add(item);
                }
            });
            return categoryTreeDtos;
        }

测试：

@Autowired
        CourseService courseService;
    
        @Test
        void context() {
            List<Course> courseList = courseService.selectTreeNode("1");
            System.out.println(courseList);
        }

结果如图遍成功完成：

![1271a9b49bb24b7994a043c704cf815d.png][]

--------------------

## 总结 ##

感谢大家的观看

此文章只是尝试练习递归查分类数据，新手上路，大家多多关照。

[8ba2b711e37e4ea1bdd1949b00ed6835.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7534352cc34a4182b0bb738ffd291e22.png
[UNION]: https://so.csdn.net/so/search?q=UNION&spm=1001.2101.3001.7020
[9e0191c3dda645f38543d02434f4b7d1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/679dcbe33bab43529977da2dc3b4fd66.png
[18cf143b1b7e4732a53a3b8aea170cce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7fd7918100db4fabb61bd91895b92c19.png
[1271a9b49bb24b7994a043c704cf815d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/b0ec3f26e6c345d3af504dd62e0241b3.png