MySQL嵌套层次结构选择

Question

我在 MySQL 中有一个下一个嵌套层次结构:

ROOT
|
+--Group1
|
+--Group2
|   |
|   +--SubGroup1
|   |
|   +--ABC
|
+--Group3
|  |
|  +--SubGroup2
|
+--Group4
|  |
|  +--SubGroup1
|     |
|     +--ABC

表格内容是:

mysql> select * from nest;
+----+------+------------+------+
| id | lft  | group_name | rgt  |
+----+------+------------+------+
|  1 |    1 | ROOT       |   20 |
|  2 |    2 | Group1     |    3 |
|  3 |    4 | Group2     |    9 |
|  4 |    5 | SubGroup1  |    8 |
|  5 |    6 | ABC        |    7 |
|  6 |   10 | Group3     |   13 |
|  7 |   11 | SubGroup2  |   12 |
|  8 |   14 | Group4     |   19 |
|  9 |   15 | SubGroup1  |   18 |
| 10 |   16 | ABC        |   17 |
+----+------+------------+------+

我正在尝试从 MySQL 的嵌套层次结构中选择所有树。

SELECT
    CONCAT( REPEAT(' ', COUNT(parent.group_name) - 1), node.group_name) AS group_name
FROM 
    nest AS node,
    nest AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
    node.group_name
ORDER BY
    node.lft;

并得到结果，只有第一次匹配时有重复的 child :

+----------------+
| group_name     |
+----------------+
| ROOT           |
|  Group1        |
|  Group2        |
|      SubGroup1 |
|        ABC     |
|  Group3        |
|   SubGroup2    |
|  Group4        |
+----------------+

我如何才能获得 Group4 的重复结果，与 Group2 类似？像这样:

+----------------+
| group_name     |
+----------------+
| ROOT           |
|  Group1        |
|  Group2        |
|      SubGroup1 |
|        ABC     |
|  Group3        |
|   SubGroup2    |
|  Group4        |
|      SubGroup1 |
|        ABC     |
+----------------+

谢谢。

Answer 1

改变

GROUP BY
node.group_name

到

GROUP BY
node.id

像这样

SELECT
    CONCAT( REPEAT(' ', COUNT(parent.group_name) - 1), node.group_name) AS group_name
FROM 
    nest AS node,
    nest AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
    node.id
ORDER BY
    node.lft