为什么此查询不起作用?(MySQL 8.0.28)

Question

我有以下两个表，正在尝试递归查询。下面是给我一个错误的表和查询。

CREATE TABLE tags (
tag_id INTEGER,
tag_name VARCHAR(50) NOT NULL,
tag_descrip VARCHAR(200),
tag VARCHAR(200) NOT NULL,
CONSTRAINT tags_pk PRIMARY KEY (tag_id )
);
CREATE TABLE tag_tree (
tag_id INTEGER,
parent_tag_id INTEGER,
CONSTRAINT tag_tree_pk UNIQUE (tag_id, parent_tag_id),
CONSTRAINT tag_tree_tags_fk FOREIGN KEY (tag_id)
REFERENCES tags(tag_id),
CONSTRAINT parent_tag_tags FOREIGN KEY (parent_tag_id)
REFERENCES tags(tag_id),
CONSTRAINT parent_tag_tag_tree_fk FOREIGN KEY (parent_tag_id)
REFERENCES tag_tree(tag_id)
);
WITH RECURSIVE tags_and_their_parents (
tag_id,
parent_tag_id,
depth)
AS (
SELECT tag_id,
parent_tag_id,
parent_tag_id,
0 AS depth
FROM tag_tree
WHERE parent_tag_id IS NULL
UNION ALL
SELECT tag_tree.tag_id,
tag_tree.parent_tag_id,
tags_and_their_parents.tag_id,
tags_and_their_parents.depth + 1
FROM tag_tree
INNER JOIN tags_and_their_parents
ON tag_tree.parent_tag_id = tags_and_their_parents.tag_id
)
SELECT * FROM tags_and_their_parents
ORDER BY depth;

错误：

您的SQL语法有错误；请查看与MySQL服务器版本相对应的手册，以获得在第18行"附近使用的正确语法(第18行是INNER JOIN(。

我遵循了另一篇文章中的确切语法(如何创建MySQL层次递归查询？(，所以我不知道哪里出了问题。如有任何帮助，我们将不胜感激！

Answer 1

链接项在其查询的三个部分中正好显示三列。但这里有三列。。。

WITH RECURSIVE tags_and_their_parents (
tag_id,
parent_tag_id,
depth)
AS ( ...

这里有四列。。。

SELECT tag_id,
parent_tag_id,
parent_tag_id,
0 AS depth

这里是

SELECT tag_tree.tag_id,
tag_tree.parent_tag_id,
tags_and_their_parents.tag_id,
tags_and_their_parents.depth + 1

删除多余的列可以修复您的语法错误fiddle。

WITH RECURSIVE tags_and_their_parents (
tag_id,
parent_tag_id,
depth)
AS (
SELECT tag_id,
parent_tag_id,
--        parent_tag_id,
0 AS depth
FROM tag_tree
WHERE parent_tag_id IS NULL
UNION ALL
SELECT tag_tree.tag_id,
tag_tree.parent_tag_id,
---       tags_and_their_parents.tag_id,
tags_and_their_parents.depth + 1
FROM tag_tree
INNER JOIN tags_and_their_parents
ON tag_tree.parent_tag_id = tags_and_their_parents.tag_id

您可能希望将第四列命名为自己的名称(可能是ancestor_tag_id？(，如下所示。小提琴

WITH RECURSIVE tags_and_their_parents (
tag_id,
parent_tag_id,
ancestor_tag_id,  -- ADDED
depth)
AS (
SELECT tag_id,
parent_tag_id,
parent_tag_id ancestor_tag_id,  --CHANGED
0 AS depth
FROM tag_tree
WHERE parent_tag_id IS NULL
UNION ALL
SELECT tag_tree.tag_id,
tag_tree.parent_tag_id,
tags_and_their_parents.tag_id ancestor_tag_id,  --CHANGED
tags_and_their_parents.depth + 1
FROM tag_tree
INNER JOIN tags_and_their_parents

这些递归CTE的语法错误报告令人抓狂，毫无用处，嗯？：-(