mysql树形查询详解

╰半橙微兮° 2023-01-11 01:10 4阅读 0赞

使用的核心函数 [FIND\_IN\_SET()][FIND_IN_SET]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NDYyNjk4_size_16_color_FFFFFF_t_70]  
sql

create table `dept` (
        `code` varchar (255),
        `name` varchar (255),
        `parent_code` varchar (255)
    ); 
    insert into `dept` (`code`, `name`, `parent_code`) values('C105','总公司',null);
    insert into `dept` (`code`, `name`, `parent_code`) values('C105001','一分公司','C105');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105002','二分公司','C105');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105003','三分公司','C105');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105001001','规划处','C105001');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105001002','信息部','C105001');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105001002020','技术中心','C105001002');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105002090','塔里木','C105002');
    insert into `dept` (`code`, `name`, `parent_code`) values('C105003008','长庆处','C105003');

查询sql

SELECT * FROM dept WHERE FIND_IN_SET(code, getchildrenlist('C105'));

创建函数

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildrenList`(pcode varchar(255)) RETURNS varchar(1000) CHARSET utf8
    begin
    declare re varchar(1000);
    declare parent varchar(1000);
     
    set re = '$';
    set parent =pcode ;
     
    while parent is not null do
    set re = concat(re,',',parent); -- 包含自身
    select group_concat(code) into parent from `dept` where find_in_set(parent_code,parent)>0;
    end while;
    return re;
    end

核心代码

select group_concat(code) into parent from `dept` where find_in_set(parent_code,parent)>0;

`group_concat` 去重  
`where find_in_set(parent_code,parent)>0`递归

[FIND_IN_SET]: https://www.cnblogs.com/mytzq/p/7090197.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NDYyNjk4_size_16_color_FFFFFF_t_70]: /images/20221119/adcf58b60b204c6eae8afb363f429dfd.png