根据你的需求，需要根据 level 字段判断层级，然后基于 - 分割 dept_path 来提取四个层级的部门信息。以下是几种SQL写法：
方案一：使用 CASE WHEN + SUBSTRING_INDEX（推荐）
SELECT
dept_name AS original_dept,
level,
dept_path,
-- 提取第一层部门（第一个-之前的部分）
CASE
WHEN level = '一层部门' THEN dept_path
ELSE SUBSTRING_INDEX(dept_path, '-', 1)
END AS dept1,
-- 提取第二层部门（第一个-和第二个-之间的部分）
CASE
WHEN level = '一层部门' THEN NULL
WHEN level = '二级部门' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 2), '-', -1)
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 2), '-', -1)
END AS dept2,
-- 提取第三层部门（第二个-和第三个-之间的部分）
CASE
WHEN level IN ('一层部门', '二级部门') THEN NULL
WHEN level = '三级部门' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 3), '-', -1)
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 3), '-', -1)
END AS dept3,
-- 提取第四层部门（第三个-之后的部分）
CASE
WHEN level IN ('一层部门', '二级部门', '三级部门') THEN NULL
WHEN level = '四级部门' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', -1), '-', -1)
ELSE NULL
END AS dept4
FROM department_table;
方案二：更清晰的 CASE WHEN 嵌套
SELECT
dept_name,
level,
dept_path,
-- 第一层总是取第一个元素
SUBSTRING_INDEX(dept_path, '-', 1) AS dept1,
-- 第二层：如果是一级部门则为NULL，否则取第二个元素
CASE
WHEN level = '一层部门' THEN NULL
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 2), '-', -1)
END AS dept2,
-- 第三层：如果是二级及以下部门则为NULL，否则取第三个元素
CASE
WHEN level IN ('一层部门', '二级部门') THEN NULL
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 3), '-', -1)
END AS dept3,
-- 第四层：只有四级部门才取第四个元素
CASE
WHEN level = '四级部门' THEN SUBSTRING_INDEX(dept_path, '-', -1)
ELSE NULL
END AS dept4
FROM department_table;
方案三：使用 IF 函数（更简洁）
SELECT
dept_name,
level,
dept_path,
SUBSTRING_INDEX(dept_path, '-', 1) AS dept1,
IF(level <> '一层部门', SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 2), '-', -1), NULL) AS dept2,
IF(level IN ('三级部门', '四级部门'), SUBSTRING_INDEX(SUBSTRING_INDEX(dept_path, '-', 3), '-', -1), NULL) AS dept3,
IF(level = '四级部门', SUBSTRING_INDEX(dept_path, '-', -1), NULL) AS dept4
FROM department_table;
方案四：动态解析（适用于任意层级）
如果你想要更通用的解决方案，可以创建一个函数来解析路径：
-- 创建函数（MySQL 8.0+ 支持JSON_TABLE，或者自定义函数）
DELIMITER $$
CREATE FUNCTION get_dept_by_level(path VARCHAR(255), level_num INT)
RETURNS VARCHAR(100)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(100);
DECLARE path_count INT;
SET path_count = LENGTH(path) - LENGTH(REPLACE(path, '-', '')) + 1;IF level_num > path_count THENRETURN NULL;
END IF;IF level_num = 1 THENSET result = SUBSTRING_INDEX(path, '-', 1);
ELSEIF level_num = path_count THENSET result = SUBSTRING_INDEX(path, '-', -1);
ELSESET result = SUBSTRING_INDEX(SUBSTRING_INDEX(path, '-', level_num), '-', -1);
END IF;RETURN result;
END$$
DELIMITER ;
-- 使用函数查询
SELECT
dept_name,
level,
dept_path,
get_dept_by_level(dept_path, 1) AS dept1,
get_dept_by_level(dept_path, 2) AS dept2,
get_dept_by_level(dept_path, 3) AS dept3,
get_dept_by_level(dept_path, 4) AS dept4
FROM department_table;
示例数据和测试结果
假设有测试数据：
-- 测试数据
SELECT '人事一部' as dept_path, '一层部门' as level
UNION ALL SELECT '人事一部-人事二部', '二级部门'
UNION ALL SELECT '人事一部-人事二部-人事三部', '三级部门'
UNION ALL SELECT '人事一部-人事二部-人事三部-人事四部', '四级部门';
使用方案二的查询结果：
dept_name level dept_path dept1 dept2 dept3 dept4
人事一部 一层部门 人事一部 人事一部 NULL NULL NULL
人事一部-人事二部 二级部门 人事一部-人事二部 人事一部 人事二部 NULL NULL
人事一部-人事二部-人事三部 三级部门 人事一部-人事二部-人事三部 人事一部 人事二部 人事三部 NULL
人事一部-人事二部-人事三部-人事四部 四级部门 人事一部-人事二部-人事三部-人事四部 人事一部 人事二部 人事三部 人事四部
关键点说明
- SUBSTRING_INDEX 函数：
◦ SUBSTRING_INDEX(str, delim, count)：按分隔符截取字符串
◦ count > 0：从左开始截取第count个分隔符之前的部分
◦ count < 0：从右开始截取第|count|个分隔符之后的部分
- 处理逻辑：
◦ 无论什么层级，dept1 总是取第一个部门
◦ dept2 只在二级及以上部门显示
◦ dept3 只在三级及以上部门显示
◦ dept4 只在四级部门显示
推荐使用方案二，逻辑清晰易懂，维护方便。