SELECT e2.eid, e2.ename
FROM employees e1, employees e2
WHERE e2.parent_id = e1.eid
AND e1.ename = '老宋'
查询小天的所有上司
在MySQL8.0以后的版本,使用递归CTE可以轻松实现
WITH RECURSIVE Leader AS (
SELECT eid, ename, position, parent_id
FROM employees
WHERE ename = '小天'
UNION ALL
SELECT e.eid, e.ename, e.position, e.parent_id
FROM employees e
INNER JOIN Leader l
ON e.eid = l.parent_id
)
SELECT eid, ename, position FROM Leader
WITH RECURSIVE Emp AS (
SELECT eid, ename, position, parent_id
FROM employees
WHERE ename = '老王'
UNION ALL
SELECT e.eid, e.ename, e.position, e.parent_id
FROM employees e
INNER JOIN Emp emp
ON e.parent_id = emp.eid
)
SELECT eid, ename, position FROM Emp
方案二 Path Enumeration(存储路径)
Path Enumeration 路径枚举法,存储根节点到每个子节点的路径
数据库存储结构
-- ----------------------------
-- Table structure for employees2
-- ----------------------------
DROP TABLE IF EXISTS `employees2`;
CREATE TABLE `employees2` (
`eid` int(11) NOT NULL COMMENT '主键ID',
`ename` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
`position` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '位置',
`path` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所在路径',
PRIMARY KEY (`eid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employees2
-- ----------------------------
INSERT INTO `employees2` VALUES (1, '老王', '高管', '/1');
INSERT INTO `employees2` VALUES (2, '老宋', '产品部主管', '/1/2');
INSERT INTO `employees2` VALUES (3, '老牛', '技术部主管', '/1/3');
INSERT INTO `employees2` VALUES (4, '小吴', '产品A组长', '/1/2/4');
INSERT INTO `employees2` VALUES (5, '小李', '产品B组长', '/1/2/5');
INSERT INTO `employees2` VALUES (6, '小欢', '产品经理', '/1/3/6');
INSERT INTO `employees2` VALUES (7, '小小', '产品经理', '/1/3/7');
INSERT INTO `employees2` VALUES (8, '小天', '产品部员工', '/1/2/4/8');
INSERT INTO `employees2` VALUES (9, '小里', '产品部员工', '/1/2/4/9');
INSERT INTO `employees2` VALUES (10, '小黑', '产品部员工', '/1/2/5/10');
INSERT INTO `employees2` VALUES (11, '小胡', '产品部员工', '/1/2/5/11');
INSERT INTO `employees2` VALUES (12, '小丽', '技术部员工', '/1/3/6/12');
INSERT INTO `employees2` VALUES (13, '小蓝', '技术部员工', '/1/3/6/13');
INSERT INTO `employees2` VALUES (14, '小黄', '技术部员工', '/1/3/7/14');
INSERT INTO `employees2` VALUES (15, '小真', '技术部员工', '/1/3/7/15');
eid
ename
position
parent_id
1
老王
高管
/1
2
老宋
产品部主管
/1/2
3
老牛
技术部主管
/1/3
4
小吴
产品A组长
/1/2/4
5
小李
产品B组长
/1/2/5
6
小欢
产品经理
/1/3/6
7
小小
产品经理
/1/3/7
8
小天
产品部员工
/1/2/4/8
9
小里
产品部员工
/1/2/4/9
10
小黑
产品部员工
/1/2/5/10
11
小胡
产品部员工
/1/2/5/11
12
小丽
技术部员工
/1/3/6/12
13
小蓝
技术部员工
/1/3/6/13
14
小黄
技术部员工
/1/3/7/14
15
小真
技术部员工
/1/3/7/15
16
小魏
产品部员工
/1/2/4/16
SQL示例
添加节点
依旧是在小吴节点插入一个下属员工
-- 1. 插入下属员工小魏,eid为16,查询父级节点path,拼接小魏的path
INSERT INTO `employees2` (`eid`, `ename`, `position`, `path`)
SELECT 16 AS eid, '小魏' AS ename, '产品部员工' AS position, CONCAT(path, '/16') AS path
FROM `employees2`
WHERE ename = '小吴';
Adjacency List(邻接列表):优点:简单易于理解和实现,只需要一个父节点 ID 字段,容易添加、删除、移动节点。不需要维护冗余数据。缺点:查询层级关系和递归操作需要进行多次查询,效率较低。在大型树状结构中,查询深度可能导致性能问题。使用场景:适用于树状结构较小,层级深度不太深的情况,或者只需要简单的树结构操作。Path Enumeration(路径枚举):优点:相比邻接列表,路径枚举能够更快速地进行递归查询和层级操作,因为路径信息已经在节点上存储。