Mysql递归查询父/子级今天备忘个小技巧。准备工作准备建表ddl 造点数据部门表（多层级部门以自关联方式体现）

今天备忘个小技巧。

准备工作

准备建表ddl

 create table hr_organization
 (
     id         bigint unsigned auto_increment comment '主键'
         primary key,
     name       varchar(255)                       null comment '名称',
     createTime datetime default CURRENT_TIMESTAMP not null comment '创建时间',
     updateTime datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',
     parentId   int                                null
 )
     comment '机构表' charset = utf8;

    create table user
    (
        id         bigint unsigned auto_increment comment '主键ID，也是用户id'
            primary key,
        userName   varchar(80)  not null comment '用户名:',
        orgId      int          not null comment '机构id',
        createTime datetime     null comment '创建时间',
        phone      varchar(255) null
    )
        comment '用户表';

造点数据

 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (1, '外星人', 1, '2022-09-08 12:35:04', '18212129999');
 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (200, '中国人', 2, '2022-09-08 12:35:04', '18212129988');
 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (300, '北京人', 3, '2022-09-08 12:35:04', '18212129977');
 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (400, '海淀人', 4, '2022-09-08 12:35:04', '18212129966');
 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (500, '中关村人', 5, null, null);
 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (600, '北京大学人', 6, null, null);
 INSERT INTO user (id, userName, orgId, createTime, phone) VALUES (700, '清华大学人', 7, null, null);

 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (1, '宇宙总部', '2022-09-08 13:08:30', '2022-09-08 12:44:06', null);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (2, '中国大区', '2022-09-08 13:08:30', '2022-09-08 12:44:06', 1);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (3, '北京分部', '2022-09-08 13:08:30', '2022-09-08 12:44:06', 2);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (4, '海淀区分部', '2022-09-08 13:08:30', '2022-09-08 12:44:06', 3);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (5, '中关村分部', '2022-09-08 13:08:30', '2022-09-08 12:44:06', 4);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (6, '北京大学分部', '2022-09-08 13:08:30', '2022-09-08 12:45:57', 5);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (7, '海淀黄庄分部', '2022-09-08 13:08:30', '2022-09-08 12:45:57', 5);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (8, '知春路分部', '2022-09-08 13:08:30', '2022-09-08 12:46:50', 5);
 INSERT INTO hr_organization (id, name, createTime, updateTime, parentId) VALUES (9, '微软大厦', '2022-09-08 13:00:40', '2022-09-08 13:00:40', 8);

部门表（多层级部门以自关联方式体现）
人员表（与部门表通过orgId逻辑关联）

递归查询某人父部门树

脚本


-- 根据人员id所在的部门 ，查找该人所在的父部门树（含自己）（从下往上）
select a.curr_id as '当前机构id',a.p_ids as '父部门id',a.org_name as '当前机构名称',a.LEVEL as '级别',a.org_pname as '父部门名称'
          from (SELECT @temp_id                                   curr_id,
                       (select name from hr_organization where id= @temp_id ) as org_name,
                       -- 逐级往上找parentId
                       (SELECT @temp_id := GROUP_CONCAT(hr_inner.parentId)
                        FROM hr_organization hr_inner
                        WHERE hr_inner.id = @temp_id) p_ids,
                       (select group_concat(name) from hr_organization where id= @temp_id ) as org_pname,
                       @l := @l + 1 AS                            LEVEL
                FROM hr_organization,
                     (SELECT @temp_id := (select orgId from user where  id =700), @l := 0) temp
                order by LEVEL asc
               ) a
          where a.curr_id is not null order by  a.LEVEL desc ;

效果

递归查询某人子部门树

脚本


-- 根据人员id所在的部门 ，查找该人所在的子部门树（含自己）（从上往下）
select a.p_ids as '当前机构id',a.curr_id as '子部门id',a.org_name as '当前机构名称',a.LEVEL as '级别',a.org_cname as '子部门名称'
          from (SELECT @temp_id                                   p_ids,
                       (select name from hr_organization where id= @temp_id ) as org_name,
                       (select GROUP_CONCAT(name) from hr_organization where parentId= @temp_id ) as org_cname,

                       (SELECT @temp_id := GROUP_CONCAT(hr_inner.id)
                        FROM hr_organization hr_inner
                        WHERE hr_inner.parentId = @temp_id) curr_id,
                       @l := @l + 1 AS                            LEVEL
                FROM hr_organization,
                     (SELECT @temp_id := (select orgId from user where  id =200), @l := 0) temp
                order by LEVEL asc
               ) a
inner  join  hr_organization u2
ON FIND_IN_SET(u2.id, a.p_ids)
          where a.curr_id is not null order by  a.curr_id asc;

效果

总结

这样建变量和临时表的方式可能用不上索引，我们这里暂且不讨论他的性能问题。