[MySQL] MySQL 查询树结构方式

create table `tree`  (
  `id` bigint(11) not null,
  `pid` bigint(11) null default null,
  `name` varchar(255) character set utf8 collate utf8_general_ci null default null,
  primary key (`id`) using btree
) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic;
insert into `tree` values (1, 0, '中国');
insert into `tree` values (2, 1, '四川省');
insert into `tree` values (3, 2, '成都市');
insert into `tree` values (4, 3, '武侯区');
insert into `tree` values (5, 4, '红牌楼');
insert into `tree` values (6, 1, '广东省');
insert into `tree` values (7, 1, '浙江省');
insert into `tree` values (8, 6, '广州市');

create function `get_child_node`(rootid varchar(100))   
returns varchar(2000)  
begin  
declare str varchar(2000);  
declare cid varchar(100);   
set str = '$';   
set cid = rootid;   
while cid is not null do   
  set str = concat(str, ',', cid);   
  select group_concat(id) into cid from tree where find_in_set(pid, cid);   
end while;   
return str;   
end

select * from tree where find_in_set(id, get_child_node(2));

create function `get_parent_node`(rootid varchar(100))   
returns varchar(1000)   
begin  
declare fid varchar(100) default '';   
declare str varchar(1000) default rootid;   
  
while rootid is not null do   
  set fid =(select pid from tree where id = rootid);   
  if fid is not null then  
    set str = concat(str, ',', fid);   
    set rootid = fid;   
  else  
    set rootid = fid;   
  end if;   
end while;   
return str;  
end

select * from tree where find_in_set(id, get_parent_node(5));

public class treenodedto {
  
  private string id;
  private string parentid;
  private string name;
  private list<treenodedto> children = new arraylist<>();
  public void add(treenodedto node) {
    if ("0".equals(node.parentid)) {
      this.children.add(node);
    } else if (node.parentid.equals(this.id)) {
      this.children.add(node);
    } else {
      //递归调用add()添加子节点
      for (treenodedto tmp_node : children) {
        tmp_node.add(node);
      }
    }
  }
 }

list<treenodedto> list = dbmapper.getnodelist();
arraylist<treenodedto> rootnodes = new arraylist<>();
map<integer, treenodedto> map = new hashmap<>();
for (treenodedto node :list) {
  map.put(node.getid(), node);
  integer parentid = node.getparentid();
  // 判断是否有父节点 (没有父节点本身就是个父菜单)
  if (parentid.equals('0')){
    rootnodes.add(node);
    // 找出不是父级菜单的且集合中包括其父菜单id
  } else if (map.containskey(parentid)){
    map.get(parentid).getchildren().add(node);
  }
}

start with connect by prior

create table `company_inf` (
  `id` varchar(32) collate utf8mb4_unicode_ci default null,
  `name` varchar(255) collate utf8mb4_unicode_ci default null,
  `parent_id` varchar(32) collate utf8mb4_unicode_ci default null
)

insert into company_inf values ('1','总经理王大麻子','1');
insert into company_inf values ('2','研发部经理刘大瘸子','1');
insert into company_inf values ('3','销售部经理马二愣子','1');
insert into company_inf values ('4','财务部经理赵三驼子','1');
insert into company_inf values ('5','秘书员工j','1');
insert into company_inf values ('6','研发一组组长吴大棒槌','2');
insert into company_inf values ('7','研发二组组长郑老六','2');
insert into company_inf values ('8','销售人员g','3');
insert into company_inf values ('9','销售人员h','3');
insert into company_inf values ('10','财务人员i','4');
insert into company_inf values ('11','开发人员a','6');
insert into company_inf values ('12','开发人员b','6');
insert into company_inf values ('13','开发人员c','6');
insert into company_inf values ('14','开发人员d','7');
insert into company_inf values ('15','开发人员e','7');
insert into company_inf values ('16','开发人员f','7');

select *
from t_portal_authority
start with id='1'
connect by prior id = parent_id

create function getchild(parentid varchar(1000))
returns varchar(1000)
begin
  declare otemp varchar(1000);
  declare otempchild varchar(1000);
  set otemp = '';
  set otempchild =parentid;
  while otempchild is not null do
    if otemp != '' then
      set otemp = concat(otemp,',',otempchild);
    else
      set otemp = otempchild;
    end if;
    select group_concat(id) into otempchild from company_inf where parentid<>id and find_in_set(parent_id,otempchild)>0;
  end while;
return otemp;
end

select * from company_inf where find_in_set(id,getchild('2'));