使用 with recursive 递归查询实现部门人数统计和组织架构全名称展示

表结构：

-- 部门表
create table department(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '唯一编号',
  dep_level tinyint not null COMMENT '部门层级（1：一级，2：二级，依次类推）',
  dep_name VARCHAR(200) NOT NULL COMMENT '部门名称',
  pid INT COMMENT '父级部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';


-- 员工表
create table employee(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '唯一编号',
  name VARCHAR(20) NOT NULL COMMENT '员工姓名',
  job_title varchar(100) NOT NULL COMMENT '职位名称',
  age INT COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';


-- 部门与员工关联表
create table department_employee(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '唯一编号',
  dep_id BIGINT not null COMMENT '部门编号',
  emp_id INT NOT NULL COMMENT '员工编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门与员工关联表';



-- 插入数据
insert into department(id, dep_level, dep_name, pid) values(1, 1, '产品设计部', null);
insert into department(id, dep_level, dep_name, pid) values(2, 2, '产品部', 1);
insert into department(id, dep_level, dep_name, pid) values(3, 3, '产品A部', 2);
insert into department(id, dep_level, dep_name, pid) values(4, 3, '产品B部', 2);

insert into employee(id, name, job_title, age) values(1, '张三', '总监', 40);
insert into employee(id, name, job_title, age) values(2, '李四', '经理', 36);
insert into employee(id, name, job_title, age) values(3, '王五', '负责人', 30);
insert into employee(id, name, job_title, age) values(4, '二麻子', '设计', 24);
insert into employee(id, name, job_title, age) values(5, '狗蛋', '设计', 26);

insert into department_employee(dep_id, emp_id) values(1, 1);
insert into department_employee(dep_id, emp_id) values(2, 2);
insert into department_employee(dep_id, emp_id) values(3, 3);
insert into department_employee(dep_id, emp_id) values(3, 4);
insert into department_employee(dep_id, emp_id) values(4, 5);

统计产品设计部总人数（包括子部门）：

with recursive temp as (
select dep.id, dep.pid, d_cnt.emp_count from department dep left join (select dep_id, count(*) as emp_count from department_employee group by dep_id) d_cnt
on d_cnt.dep_id = dep.id 
where dep.id = 1
union all 
 select t.id, t.pid, t.emp_count from 
(select dep.id, dep.pid, d_cnt.emp_count
    from (select dep_id, count(*) as emp_count from department_employee group by dep_id) d_cnt
    right join department dep on d_cnt.dep_id = dep.id) t 
inner join temp t2 on t2.id = t.pid 
)
select COALESCE(sum(emp_count),0) AS emp_count from temp

查询产品B部的全名称：

with recursive type_cte as (
    select id, dep_name,pid  from department  where id = 4
    union all
    select t.id,concat(t.dep_name,'-',type_cte2.dep_name),t.pid
    from department t
             inner join type_cte type_cte2 on t.id  = type_cte2.pid
    )
    select dep_name from type_cte where pid is null;