-- 创建部门表
CREATE TABLE department (
? ? dept_id INT PRIMARY KEY AUTO_INCREMENT,
? ? parent_id INT,
? ? name VARCHAR(50)
);
?
-- 添加测试数据
INSERT INTO department (name, parent_id) VALUES ('公司', NULL), ('技术部', 1), ('市场部', 1), ('研发组', 2), ('产品组', 2), ('运营组', 3), ('客服组', 3);
?
-- 定义递归函数
DELIMITER $$
CREATE FUNCTION getDepartmentPaths(deptId INT) RETURNS TEXT BEGIN
? ? DECLARE resultText TEXT DEFAULT ''; -- 保存结果文本
? ??
? ? SELECT GROUP_CONCAT(name SEPARATOR ' > ') INTO resultText FROM department WHERE find_in_set(parent_id, @path := CONCAT(@path, ',', dept_id)) IS NOT NULL;
? ??
? ? IF CHARACTER_LENGTH(resultText) = 0 THEN
? ? ? ? SET resultText = '-';
? ? END IF;
? ??
? ? RETURN resultText;
END$$
DELIMITER ;
?
-- 调用递归函数获取部门路径
SELECT dept_id, getDepartmentPaths(dept_id) AS path FROM department ORDER BY dept_id ASC;