SQL查询指定员工所有层级上级的更优实现方法咨询
员工层级上级查询最优实现方案
你当前的硬编码写法仅支持固定层级查询,针对这类树形结构的遍历需求,递归公共表表达式(CTE) 是通用性最高的实现方案,支持任意深度的层级查询,无需修改代码即可适配层级变动。
适用范围
支持所有兼容SQL标准递归CTE的主流数据库,包括SQL Server、MySQL 8.0+、PostgreSQL、Oracle 11gR2等。
实现代码
-- 定义待查询的目标员工ID DECLARE @TargetEmployeeID INT = 1000000; WITH RecursiveSuperior AS ( -- 锚点查询:获取目标员工的直接上级 SELECT SuperiorID, 1 AS hierarchy_level FROM EmployeeTable WHERE Employee_ID = @TargetEmployeeID UNION ALL -- 递归查询:逐层向上获取上级的上级 SELECT e.SuperiorID, rs.hierarchy_level + 1 FROM EmployeeTable e INNER JOIN RecursiveSuperior rs ON e.Employee_ID = rs.SuperiorID WHERE e.SuperiorID IS NOT NULL -- 遍历到最高负责人(无上级)时停止 ) -- 输出结果,按层级从近到远排序 SELECT SuperiorID AS 上级员工ID, hierarchy_level AS 层级 FROM RecursiveSuperior ORDER BY hierarchy_level ASC;
运行以上代码,针对你给出的样例数据,输出结果如下:
| 上级员工ID | 层级 |
|---|---|
| 1000001 | 1 |
| 1000002 | 2 |
| 1000003 | 3 |
| 1000004 | 4 |
| 1000005 | 5 |
低版本数据库兼容方案
如果你使用的是不支持CTE的低版本数据库(如MySQL 5.x),可以通过存储过程循环遍历实现:
DELIMITER // CREATE PROCEDURE GetAllSuperiors(IN target_emp_id INT) BEGIN DECLARE current_superior INT DEFAULT target_emp_id; DECLARE current_level INT DEFAULT 1; CREATE TEMPORARY TABLE IF NOT EXISTS temp_superiors (superior_id INT, level INT); WHILE current_superior IS NOT NULL DO SELECT SuperiorID INTO current_superior FROM EmployeeTable WHERE Employee_ID = current_superior; IF current_superior IS NOT NULL THEN INSERT INTO temp_superiors VALUES(current_superior, current_level); SET current_level = current_level + 1; END IF; END WHILE; SELECT * FROM temp_superiors ORDER BY level ASC; DROP TEMPORARY TABLE IF EXISTS temp_superiors; END // DELIMITER ; -- 调用存储过程查询 CALL GetAllSuperiors(1000000);
内容的提问来源于stack exchange,提问作者Rigel1121
相关产品推荐
相关产品推荐

