mysql实现递归查询sql及实际案例

女爷i 2021-12-18 12:51 632阅读 0赞

### **数据准备：** ###

表结构：

CREATE TABLE `tb_dept` (
      `id` bigint(20) NOT NULL,
      `parent_id` bigint(20) DEFAULT NULL,
      `dept_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
      `dept_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_parent_id` (`parent_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

插入测试数据：

INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (1, NULL, '01', '葫芦科技');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (2, 1, '001', '运营部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (3, 1, '002', '技术部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (4, 1, '003', '产品部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (5, 1, '004', '设计部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (6, 2, '00101', '运营一部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (7, 2, '00102', '运营二部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (8, 3, '00201', '技术一部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (9, 3, '00202', '技术二部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (10, 3, '00203', '技术三部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (11, 3, '00204', '技术四部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (12, 4, '00301', '运营一部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (13, 4, '00302', '运营二部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (14, 4, '00303', '运营三部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (15, 5, '00401', '设计一部');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (16, 8, '0020101', '技术一部一组');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (17, 8, '0020102', '技术一部二组');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (18, 9, '0020201', '技术二部一组');
    INSERT INTO `test`.`tb_dept`(`id`, `parent_id`, `dept_code`, `dept_name`) VALUES (19, 10, '0020301', '技术三部一组');

数据信息：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70][]

### **一、递归查询某个部门的所有子部门：** ###

> SELECT \* FROM (SELECT id, parent\_id, dept\_code, dept\_name FROM tb\_dept WHERE parent\_id IS NOT NULL) rd,(SELECT @pid := 3) pd  
> where FIND\_IN\_SET( parent\_id, @pid ) > 0 AND @pid := concat( @pid, ',', id );

@pid := 3 初始赋值，即你需要查询其子部门的某个部门

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70 1][]

### **二、查询某个部门的所有父部门：** ###

> SELECT  
>     tdd.id,tdd.dept\_code,tdd.dept\_name   
> FROM  
>     (  
>     SELECT  
>         ( SELECT @cid := parent\_id FROM tb\_dept WHERE id = @cid ) AS pid   
>     FROM  
>         ( SELECT @cid := 19 ) cs,  
>         tb\_dept td   
>     ) tp  
>     LEFT JOIN tb\_dept tdd ON tdd.id = tp.pid   
> WHERE  
>     tp.pid IS NOT NULL

@cid := 19 初始赋值，即你需要查询其父部门的某个部门

运行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70 2][]

mysql中 :=和=的区别： [https://blog.csdn.net/HXNLYW/article/details/94442220][https_blog.csdn.net_HXNLYW_article_details_94442220]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70]: /images/20211218/e5634b5518684847b97ca770d5111d73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70 1]: /images/20211218/4e803ff390d943a58696890b3c56ac08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0hYTkxZVw_size_16_color_FFFFFF_t_70 2]: /images/20211218/e90efa0dab6e43a4855d8f6bff037cad.png
[https_blog.csdn.net_HXNLYW_article_details_94442220]: https://blog.csdn.net/HXNLYW/article/details/94442220