时间戳

首页 > 解决方案 > 不使用赋值运算符的 MySQL 分层树状排序查询

mysql - 不使用赋值运算符的 MySQL 分层树状排序查询

问题描述

我有基于explainextended的分层树结构排序查询

现在我需要使用“WITH RECURSIVE”更改查询，因为 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”来获得这个结果？

标签： mysqlhierarchical-datarecursive-querytreesort

解决方案

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

推荐阅读