我有两列,一个雇员的职位ID和经理职位之一。我试图检查层次结构内的任何循环引用,但我没有得到任何输出。我还需要确保查询足够好,以说明大量的职位数据。请帮助,这里是我的代码
WITH RECURSIVE FindRoot AS
(
SELECT EE_Position,Manager_Position, CAST(EE_Position AS CHAR(500)) Path, 0 Distance
FROM Manger_Table
UNION ALL
SELECT C.EE_Position, P.Manager_Position, CONCAT(C.Path,' > ',CAST(P.EE_Position AS CHAR(500))), C.Distance + 1
FROM Manger_Table P
JOIN FindRoot C
ON C.Manager_Position = P.EE_Position AND P.Manager_Position <> P.EE_Position AND C.Manager_Position <> C.EE_Position
)
SELECT *
FROM FindRoot R
WHERE R.EE_Position = R.Manager_Position
AND R.Manager_Position <> 0
AND R.Distance > 0;
有人也可以请教我,如果它确实检测到一个循环引用,我们如何显示它?像CASE当x = x,然后'循环引用检测'作为'错误消息'?
TIA!
1条答案
按热度按时间iqxoj9l91#
演示
| 圆形_已找到|
| - ------|
| 无|
| 圆形_已找到|
| - ------|
| 1个|
fiddle