我有一个基于解释扩展的层次树结构排序查询
现在我需要用";具有递归性";由于MySQL 8+将来将删除赋值运算符(:=(。
以下是我的问题示例:
CREATE TABLE `test`.`TestTable` (
`id` INT NOT NULL,
`parentid` INT NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('1', '0');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('2', '1');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('3', '1');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('4', '2');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('5', '3');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('6', '2');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('7', '2');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('8', '3');
INSERT INTO `test`.`TestTable` (`id`, `parentid`) VALUES ('9', '3');
WITH RECURSIVE CTE AS (
SELECT id, parentid, 1 AS level
FROM test.TestTable
WHERE id=1
UNION ALL
SELECT p.id, p.parentid, level + 1
FROM CTE
INNER JOIN test.TestTable p ON p.parentid=CTE.id
)
SELECT * FROM CTE;
输出显示如下
id parentid level
1 0 1
2 1 2
3 1 2
4 2 3
5 3 3
6 2 3
7 2 3
8 3 3
9 3 3
然而,我想要的是显示树状结果,看起来像
id parentid level
1 0 1
2 1 2
4 2 3
6 2 3
7 2 3
3 1 2
5 3 3
8 3 3
9 3 3
有没有一种方法可以使用";与递归";?
WITH RECURSIVE CTE AS (
SELECT id, parentid, 1 AS level, CAST(id AS CHAR) path
FROM TestTable
WHERE id=1
UNION ALL
SELECT p.id, p.parentid, level + 1, CONCAT_WS(' ', CTE.path, p.id)
FROM CTE
INNER JOIN TestTable p ON p.parentid=CTE.id
)
SELECT *
FROM CTE
ORDER BY path;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5e002350885533d432ffc272ef3917eb