表结构:
-- 部门表
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;