无限下钻级联部门数据查询SQL

喜欢ヅ旅行 2024-04-01 08:52 61阅读 0赞

> 需求背景：项目要求实现自定义的分级部门，且可灵活往下扩展子级。  
>   
> **这里以军队的部门划分为例：司令 - 军长 - 师长 - 旅长 - 团长 - 营长 - 连长 - 排长**
> 
>  *  指定用户所属部门为司令，可以查询到所有下钻子级的部门信息；
>  *  如果指定所属部门为营长，则可查询到营长，连长，排长等信息。

#### 1. 创建表 k\_dept ####

CREATE TABLE `k_dept` (
      `id` int NOT NULL AUTO_INCREMENT,
      `parent_id` int NOT NULL COMMENT '父级部门id',
      `name` varchar(255) NOT NULL COMMENT '部门名称',
      `desc` varchar(255) DEFAULT NULL COMMENT '部门描述',
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3;

#### 2. 插入数据 ####

insert into k_dept (id, parent_id, name) VALUES (1, 0, '司令');
    insert into k_dept (id, parent_id, name) VALUES (2, 1, '军长');
    insert into k_dept (id, parent_id, name) VALUES (3, 2, '师长A');
    insert into k_dept (id, parent_id, name) VALUES (4, 2, '师长B');
    insert into k_dept (id, parent_id, name) VALUES (5, 3, '旅长');
    insert into k_dept (id, parent_id, name) VALUES (6, 5, '团长');
    insert into k_dept (id, parent_id, name) VALUES (7, 6, '营长A');
    insert into k_dept (id, parent_id, name) VALUES (8, 6, '营长B');
    insert into k_dept (id, parent_id, name) VALUES (9, 7, '连长A');
    insert into k_dept (id, parent_id, name) VALUES (10, 8, '连长B');
    insert into k_dept (id, parent_id, name) VALUES (11, 8, '连长C');
    insert into k_dept (id, parent_id, name) VALUES (12, 9, '排长A');
    insert into k_dept (id, parent_id, name) VALUES (13, 11, '排长B');

#### 3. 递归查询语句 ####

/**
    * 通过顶级部门查询所有下钻子级的部门数据
    * 这里设置了 @id=1，即传入的条件为用户所属部门为司令
    **/
    
    SELECT
        T3.*
    FROM
        (
            SELECT
                @id AS _ids,
                ( SELECT @id := GROUP_CONCAT( id ) FROM K_Dept WHERE FIND_IN_SET( Parent_id, @id ) ) AS T1
            FROM
                K_Dept,
                ( SELECT @id := 1 ) T4
            WHERE
                @id IS NOT NULL
        ) T2,
        K_Dept T3
    WHERE
        FIND_IN_SET( T3.id, T2._ids )
    ORDER BY id;

查询结果：  
![在这里插入图片描述][94061eb2f66b48cf8f2b6cefd9f6bb13.png]

/**
    * 通过任意中间子级部门查询其下钻子级的部门数据
    * 这里设置了 @id=8，即传入的条件为用户所属部门为营长B
    **/
    
    SELECT
        T3.*
    FROM
        (
            SELECT
                @id AS _ids,
                ( SELECT @id := GROUP_CONCAT( id ) FROM K_Dept WHERE FIND_IN_SET( Parent_id, @id ) ) AS T1
            FROM
                K_Dept,
                ( SELECT @id := 8 ) T4
            WHERE
                @id IS NOT NULL
        ) T2,
        K_Dept T3
    WHERE
        FIND_IN_SET( T3.id, T2._ids )
    ORDER BY id;

查询结果：  
![在这里插入图片描述][90275920c7954205bcb2648e0bf77257.png]

[94061eb2f66b48cf8f2b6cefd9f6bb13.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/bc1cbd222afc41eca4b724c2ad42e7c1.png
[90275920c7954205bcb2648e0bf77257.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/90e104e74bca4099b11ddafc16670e64.png