Mysql上下级结构表设计

背景

希望包含上下级数据结构，比如部门，希望从指定部门查询到所有下级和所有上级的数据。
本文从设计数据库表，相关sql诠释来

数据表

如上，我们引入一张关系表，专门存放主表的上下级关系

约定：如果没有上级部门，则parent_Id存0

操作sql

---

INSERT

在新增部门数据后,如果当前部门有上级（假设当前部门id=2000, 上级id=1000）插入关联数据如下

INSERT INTO tb_department_relation (parent_Id, children_Id) values (1000,2000)

否则

INSERT INTO tb_department_relation (parent_Id, children_Id) values (0,2000)

---

SELECT

当前表格数据如下：
部门表 tb_department

关联表 tb_department_relation

常用的查询如下：

-- 查询id=1部门的直接子节点
SELECT td.Id AS id,td.Name AS name
FROM tb_department td JOIN tb_department_relation tdr ON td.id = tdr.children_Id
WHERE tdr.parent_Id=1

-- 查询id=3部门的直接父节点
SELECT td.Id AS id,td.Name AS name
FROM tb_department td JOIN tb_department_relation tdr ON td.id = tdr.parent_Id
WHERE tdr.children_Id=3

-- 查询所有根节点(没有父节点的节点)
SELECT td.Id AS id,td.Name AS name
FROM tb_department td JOIN tb_department_relation tdr ON td.id = tdr.children_Id
WHERE tdr.parent_Id=0

-- 查询所有叶子节点(没有子节点的节点)
SELECT td.Id AS id,td.Name AS name
FROM tb_department td 
WHERE id not in (
	SELECT tdr.parent_Id AS id FROM tb_department_relation tdr
)

对应查询结果：

---

追加
得到所有上级和所有下级【点击访问】