得到员工的上级。然后再得到员工的上级的上级。诸若此类
得到员工的上级。然后再得到员工的上级的上级。诸若此类
提问于 2021-06-08 11:30:20
我有一个employees表，其中有一个列Employee_ID和SuperiorID。我想要得到某个特定员工的所有上级。如Employee_ID-1000000的上级为1000001、1000002、1000003、1000004和1000005
+--------------------------+
| Employee_ID | SuperiorID |
+--------------------------+
| 1000000 | 1000001 |
| 1000001 | 1000002 |
| 1000002 | 1000003 |
| 1000003 | 1000004 |
| 1000004 | 1000005 |
+--------------------------+Employee_ID - 1000005是该公司的总裁。
我现在有一个问题：
DECLARE @EmployeeNo AS INT = 1000000
SELECT Superior_ID from EmployeeTable WHERE Employee_ID = @EmployeeNo
UNION
SELECT Superior_ID from EmployeeTable WHERE Employee_ID = (select Superior_ID from EmployeeTable WHERE Employee_ID = @EmployeeNo)
UNION
SELECT Superior_ID from EmpoyeeTable WHERE Employee_ID = ( SELECT Superior_ID from EmployeeTable WHERE Employee_ID = (select Superior_ID from EmployeeTable WHERE Employee_ID = @EmployeeNo))有没有更好的方法来解决这个问题？
谢谢你的帮助。
回答 3
Stack Overflow用户
发布于 2021-06-08 11:38:24
是的，您需要recursive cte：
DECLARE @EmployeeNo AS INT = 1000000;
with cte as (
select Employee_ID, SuperiorID
from EmployeeTable where Employee_ID = @EmployeeNo
union all
select e.Employee_ID, e.SuperiorID
from cte join EmployeeTable e on cte.SuperiorID = e.Employee_ID
where e.Employee_ID <> '1000005' --president
)
select * from cte;Stack Overflow用户
发布于 2021-06-08 11:39:04
我们可以在这里使用递归分层查询。下面的策略是从employee 1000000开始沿着命令链向上构建完整的层次结构。递归止于employee 1000004，其上级没有进一步的记录。在下面的递归CTE中，我跟踪了一个计数器，它随着每一步的递增而递增。然后，我们使用TOP技巧只选择排名最高的记录。
WITH cte (EmployeeID, SuperiorID, n) AS (
SELECT Employee_ID, SuperiorID, 1
FROM EmployeeTable
WHERE Employee_ID = 1000000
UNION ALL
SELECT e.Employee_ID, e.SuperiorID, n+1
FROM cte t
INNER JOIN EmployeeTable e
ON t.SuperiorID = e.Employee_ID
)
SELECT TOP 1 *
FROM cte
ORDER BY n DESC;
Stack Overflow用户
发布于 2021-06-09 02:41:12
试试这个：
DECLARE @EmployeeTable TABLE (EmployeeID int, SuperiorID int);
INSERT INTO @EmployeeTable
VALUES
(1000000, 1000001)
, (1000001, 1000002)
, (1000002, 1000003)
, (1000003, 1000004)
, (1000004, 1000005)
;
DECLARE @EmployeeNo int = 1000004
, @President int = 1000005
;
WITH Employee AS
(
SELECT
EmployeeID = @EmployeeNo
UNION ALL
SELECT EmployeeID = S.SuperiorID
FROM Employee E
JOIN @EmployeeTable S ON S.EmployeeID = E.EmployeeID
)
SELECT * FROM Employee页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67881046
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号