MYSQL CTE正在检查管理层次结构上的循环引用，但未获得任何输出

00jrzges 于 2023-03-11 发布在 Mysql

关注(0)|答案(1)|浏览(106)

我有两列，一个雇员的职位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！

mysql

来源：https://stackoverflow.com/questions/75608982/mysql-cte-checking-for-circular-reference-on-managerial-hierarchy-but-not-gettin

1条答案

按热度按时间

iqxoj9l91#

演示

-- test table
CREATE TABLE test (
  id INT,   -- entity
  ref INT   -- its parent
  );

CREATE VIEW test_for_circle
  AS
WITH RECURSIVE
cte AS (
  SELECT id, ref, CAST('' AS CHAR(1024)) AS path
  FROM test
  UNION ALL
  SELECT test.id, test.ref, CONCAT_WS(',', cte.path, cte.id)
  FROM cte
  JOIN test ON cte.ref = test.id
  WHERE NOT FIND_IN_SET(cte.id, cte.path)
)
-- SELECT * FROM cte
SELECT EXISTS ( SELECT NULL
                FROM cte
                WHERE FIND_IN_SET(cte.id, cte.path)
                ) circle_found

-- test data w/o circle reference
INSERT INTO test VALUES (1, NULL), (2, 1), (3, 1), (4, 2), (5, 4), (6, 3);
SELECT * FROM test_for_circle;

| 圆形_已找到|
| - ------|
| 无|

-- add circle reference
INSERT INTO test VALUES (7,8), (8,9), (9,7);
SELECT * FROM test_for_circle;

| 圆形_已找到|
| - ------|
| 1个|
fiddle

赞(0）回复(0）举报 2023-03-11

我来回答

MYSQL CTE正在检查管理层次结构上的循环引用，但未获得任何输出

1条答案

演示

相关问题

热门标签

最新问答