您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
问答- 问题:MySQL Closure Table分层数据库-如何以正确的顺序提取信息

MySQL Closure Table分层数据库-如何以正确的顺序提取信息

MySQL Closure Table分层数据库-如何以正确的顺序提取信息

SELECT d.`iD`, d.`subsectionOf`,
       CONCAT(REPEAT('-', p.`len`), d.`name`) as hier,
       p.`len`, p.`ancestor`, p.`descendant`,
       GROUP_CONCAT(crumbs.`ancestor`) AS breadcrumbs
FROM `TreeData` AS d
JOIN `TreePaths` AS p ON d.`iD` = p.`descendant`
JOIN `TreePaths` AS crumbs ON crumbs.`descendant` = p.`descendant`
WHERE p.`ancestor` = 1
GROUP BY d.`iD`
ORDER BY breadcrumbs;

+----+--------------+---------------------+-----+----------+------------+-------------+
| iD | subsectionOf | hier                | len | ancestor | descendant | breadcrumbs |
+----+--------------+---------------------+-----+----------+------------+-------------+
|  1 |         NULL | Root A              |   0 |        1 |          1 | 1           | 
|  2 |            1 | -Item 1             |   1 |        1 |          2 | 1,2         | 
|  5 |            2 | --Item 1 Sub Item 2 |   2 |        1 |          5 | 1,2,5       | 
|  6 |            2 | --Item 1 Sub Item 1 |   2 |        1 |          6 | 1,2,6       | 
|  3 |            1 | -Item 2             |   1 |        1 |          3 | 1,3         | 
|  4 |            1 | -Item 3             |   1 |        1 |          4 | 1,4         | 
|  8 |            4 | --Item 3 Sub Item 1 |   2 |        1 |          8 | 1,4,8       | 
|  9 |            4 | --Item 3 Sub Item 2 |   2 |        1 |          9 | 1,4,9       | 
|  7 |            1 | -Item 4             |   1 |        1 |          7 | 1,7         | 
+----+--------------+---------------------+-----+----------+------------+-------------+
MySQL 2022/1/1 18:20:05 有422人围观

撰写回答

你尚未登录，登录后可以

和开发者交流问题的细节

关注并接收问题和回答的更新提醒

参与内容的编辑和改进，让解决方法与时俱进

请先登录

推荐问题

联系我

联系我

602392714

清零编程清零编程群

置顶