Mysql递归查询父/子级-CSDN博客

本文链接：https://blog.csdn.net/hzzdecsdn/article/details/126780747

本文介绍了如何使用MySQL进行递归查询，包括查询某人所在的父部门树和子部门树。通过示例SQL脚本展示了如何构建查询语句，以便获取人员所属的完整部门层级结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天备忘个小技巧。

准备工作

准备建表ddl

sql 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;

sql 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 '用户表';

造点数据

sql 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);

sql 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逻辑关联)

递归查询某人父部门树

脚本

```sql

-- 根据人员id所在的部门，查找该人所在的父部门树(含自己)(从下往上) select a.currid as '当前机构id',a.pids as '父部门id',a.orgname as '当前机构名称',a.LEVEL as '级别',a.orgpname as '父部门名称' from (SELECT @tempid currid, (select name from hrorganization where id= @tempid ) as orgname, -- 逐级往上找parentId (SELECT @tempid := GROUPCONCAT(hrinner.parentId) FROM hrorganization hrinner WHERE hrinner.id = @tempid) pids, (select groupconcat(name) from hrorganization where id= @tempid ) as orgpname, @l := @l + 1 AS LEVEL FROM hrorganization, (SELECT @tempid := (select orgId from user where id =700), @l := 0) temp order by LEVEL asc ) a where a.currid is not null order by a.LEVEL desc ; ```

效果

递归查询某人子部门树

脚本

```sql

-- 根据人员id所在的部门，查找该人所在的子部门树(含自己)(从上往下) select a.pids as '当前机构id',a.currid as '子部门id',a.orgname as '当前机构名称',a.LEVEL as '级别',a.orgcname as '子部门名称' from (SELECT @tempid pids, (select name from hrorganization where id= @tempid ) as orgname, (select GROUPCONCAT(name) from hrorganization where parentId= @tempid ) 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 hrorganization u2 ON FINDINSET(u2.id, a.pids) where a.currid is not null order by a.currid asc; ```