MySQL - 虽然存在于片段中会抛出错误

Question

我正在阅读此页面 https://www.sqlteam.com/articles/more-trees-hierarchies-in-sql (关于在 SQL 中管理分层数据的好文章)并在 MySQL 中跟进并尝试运行此 SQL 片段。

WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null) 
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
FROM Tree AS T 
INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null

除了我收到以下错误:

You have an error in your SQL syntax; it seems the error is around: 'WHILE EXISTS ( SELECT * FROM Tree WHERE Depth Is Null ) UPDATE' at line 1

我尝试根据 While exists in mysql 中的建议添加 BEGIN、END、END WHILE 等但仍然无法正常工作。

您可以尝试在 SQL 验证器(https://www.eversql.com/sql-syntax-check-validator/ 是一个很好的免费在线验证器)中运行上面的代码片段，然后查看错误。

我正在寻找关于为什么上述代码段在博客中不起作用的建议/可以更改哪些内容以获得相同的结果。

更新 - 添加更多数据

感谢@danblack 建议添加更多数据以使其成为可验证的示例。

我当前的表

Node    ParentNode  EmployeeID  Depth   Lineage
100     NULL        1001        0       /
101     100         1002        NULL    NULL
102     101         1003        NULL    NULL
103     102         1004        NULL    NULL
104     102         1005        NULL    NULL
105     102         1006        NULL    NULL

代码段运行后应该是什么样子

Node    ParentNode  EmployeeID  Depth   Lineage
100     NULL        1001        0       /
101     100         1002        1       /100/
102     101         1003        2       /100/101/
103     102         1004        3       /100/101/102/
104     102         1005        3       /100/101/102/
105     102         1006        3       /100/101/102/

@danblack 指出该片段是 Microsoft SQL，而不是 MySQL，应该使用递归 CTE 来执行此操作。这是我的尝试，但我仍然无法弄清楚。

;WITH user_count
AS
(
SELECT * FROM Tree WHERE Depth Is Null AS null_users
UNION ALL
WHILE EXISTS () 
UPDATE T SET T.depth = P.Depth + 1, 
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
FROM Tree AS T 
INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
WHERE P.Depth>=0 
AND P.Lineage Is Not Null 
AND T.Depth Is Null
)

Answer 1

在 MySQL-8.0 中:

select version();

| version() || :-------- || 8.0.13    |

CREATE TABLE tree (
  `Node` VARCHAR(3),
  `ParentNode` VARCHAR(3),
  `EmployeeID` INTEGER,
  `Depth` INTEGER,
  `Lineage` VARCHAR(16)
);

✓

INSERT INTO tree
  (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
VALUES
  ('100', NULL, '1001', 0, '/'),
  ('101', '100', '1002', NULL, NULL),
  ('102', '101', '1003', NULL, NULL),
  ('103', '102', '1004', NULL, NULL),
  ('104', '102', '1005', NULL, NULL),
  ('105', '102', '1006', NULL, NULL);

✓

WITH RECURSIVE prev AS (
SELECT * FROM tree WHERE ParentNode IS NULL
UNION
SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
FROM tree t
JOIN prev p ON t.ParentNode = p.Node
)
SELECT * FROM prev;

Node | ParentNode | EmployeeID | Depth | Lineage      :--- | :--------- | ---------: | ----: | :------------100  | null       |       1001 |     0 | /            101  | 100        |       1002 |     1 | /100/        102  | 101        |       1003 |     2 | /100/101/    103  | 102        |       1004 |     3 | /100/101/102/104  | 102        |       1005 |     3 | /100/101/102/105  | 102        |       1006 |     3 | /100/101/102/

WITH RECURSIVE prev AS (
SELECT * FROM tree WHERE ParentNode IS NULL
UNION
SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
FROM prev p
JOIN tree t ON t.ParentNode = p.Node
)
UPDATE tree t, prev p
SET t.Depth=p.Depth, t.Lineage=p.Lineage
WHERE t.Node=p.Node;

✓

SELECT * FROM tree

Node | ParentNode | EmployeeID | Depth | Lineage      :--- | :--------- | ---------: | ----: | :------------100  | null       |       1001 |     0 | /            101  | 100        |       1002 |     1 | /100/        102  | 101        |       1003 |     2 | /100/101/    103  | 102        |       1004 |     3 | /100/101/102/104  | 102        |       1005 |     3 | /100/101/102/105  | 102        |       1006 |     3 | /100/101/102/

mysql8.0 db<>fiddle here

MariaDB 做 not yet supported UPDATE in CTEs

然而，它可以用像这样的临时表来执行:

CREATE TEMPORARY TABLE newtree AS
WITH RECURSIVE prev AS (
SELECT * FROM tree WHERE ParentNode IS NULL
UNION
SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
FROM tree t JOIN prev p ON t.ParentNode = p.Node
)
SELECT Node,Depth,Lineage FROM prev;

✓

UPDATE tree t, newtree p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;

✓

SELECT * from tree

Node | ParentNode | EmployeeID | Depth | Lineage      :--- | :--------- | ---------: | ----: | :------------100  | null       |       1001 |     0 | /            101  | 100        |       1002 |     1 | /100/        102  | 101        |       1003 |     2 | /100/101/    103  | 102        |       1004 |     3 | /100/101/102/104  | 102        |       1005 |     3 | /100/101/102/105  | 102        |       1006 |     3 | /100/101/102/

MariaDB-10.3 db<>fiddle here