mysql 通过sql计算部门路径的方法

-- 创建部门表
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;