时间: 2021年03月27日
问题
需求: 给定一个组织部门的id, 计算这个组织部门深度
数据库使用MySQL, 计算嵌套组织的深度有两个办法:
- 外部代码循环查询(不推荐)
- 使用MySQL函数和存储过程进行递归查询
使用MySQL函数和存储过程计算嵌套组织深度
CREATE TABLE `organization` (
`id` BIGINT NOT NULL COMMENT ‘id主键‘,
`pid` BIGINT NOT NULL COMMENT ‘父级组织id‘,
`name` varchar(64) NOT NULL COMMENT ‘组织名‘,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘组织表‘;
-- 初始化数据
INSERT INTO organization (id, pid, `name`) VALUE(1, 0, ‘总部‘);
INSERT INTO organization (id, pid, `name`) VALUE(2, 1, ‘研发部‘);
INSERT INTO organization (id, pid, `name`) VALUE(3, 1, ‘产品部‘);
INSERT INTO organization (id, pid, `name`) VALUE(4, 1, ‘财务部‘);
INSERT INTO organization (id, pid, `name`) VALUE(5, 2, ‘后端‘);
INSERT INTO organization (id, pid, `name`) VALUE(6, 2, ‘前端‘);
INSERT INTO organization (id, pid, `name`) VALUE(7, 5, ‘Java‘);
INSERT INTO organization (id, pid, `name`) VALUE(8, 5, ‘Python‘);
INSERT INTO organization (id, pid, `name`) VALUE(9, 6, ‘Vue‘);
INSERT INTO organization (id, pid, `name`) VALUE(10, 6, ‘React‘);
-- 创建函数 countLayer
DELIMITER //
DROP FUNCTION IF EXISTS countLayer//
CREATE FUNCTION countLayer(id BIGINT) RETURNS INT
BEGIN
DECLARE counter INT DEFAULT 0;
SET max_sp_recursion_depth = 255;
CALL countLayerHelper(id, counter);
RETURN counter - 1;
END//
-- 创建存储过程 countLayerHelper
DROP PROCEDURE IF EXISTS countLayerHelper;
CREATE PROCEDURE countLayerHelper(IN id BIGINT, INOUT counter INT)
BEGIN
IF EXISTS (SELECT 1 FROM organization o WHERE o.id = id) THEN
BEGIN
DECLARE pid BIGINT DEFAULT 0;
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE _cursor CURSOR FOR SELECT o.pid FROM organization o WHERE o.id = id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET counter = counter + 1;
OPEN _cursor;
read_loop: LOOP
FETCH _cursor INTO pid;
IF done THEN LEAVE read_loop;
END IF;
CALL countLayerHelper(pid, counter);
END LOOP;
CLOSE _cursor;
END;
END IF;
END;
组织部门架构
图1 组织部门架构
测试
SELECT countLayer(0)
结果: -1
SELECT countLayer(-1)
结果: -1
SELECT countLayer(11)
结果: -1
SELECT countLayer(1)
结果: 0
SELECT countLayer(3)
结果: 1
SELECT countLayer(6)
结果: 2
SELECT countLayer(8)
结果: 3
注意
如果形参或自定义变量和查询的字段同名则需要查询时将表重命名
SELECT 1 FROM organization o WHERE o.id = id;
SELECT o.pid FROM organization o WHERE o.id = id;
参考
php - 使用mysql的经理下的员工递归计数. https://www.coder.work/article/2481804