时间戳

首页 > 解决方案 > 获取 mysql 中特定树的叶节点

mysql - 获取 mysql 中特定树的叶节点

问题描述

我有一张如下表。

+------+----------------------+-----------+
|  Id  |       category       | parent_id |
+------+----------------------+-----------+
|    1 | ELECTRONICS          | NULL      |
|    2 | TELEVISIONS          | 1         |
|    3 | TUBE                 | 2         |
|    4 | LCD                  | 2         |
|    5 | PLASMA               | 2         |
|    6 | PORTABLE ELECTRONICS | 1         |
|    7 | MP3 PLAYERS          | 6         |
|    8 | FLASH                | 7         |
|    9 | CD PLAYERS           | 6         |
|   10 | 2 WAY RADIOS         | 6         |
|  100 | ELECTRONICS          | NULL      |
|  200 | TELEVISIONS          | 100       |
|  300 | TUBE                 | 200       |
|  400 | LCD                  | 200       |
|  500 | PLASMA               | 200       |
|  600 | PORTABLE ELECTRONICS | 100       |
|  700 | MP3 PLAYERS          | 600       |
|  800 | FLASH                | 700       |
|  900 | CD PLAYERS           | 600       |
| 1000 | 2 WAY RADIOS         | 600       |
+------+----------------------+-----------+

有两棵树存储为邻接列表。即使有多棵树，我也想为一棵树获取叶节点，给定根节点。

我正在关注http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/如果整个表是树状的，我们可以获得叶节点。

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

标签： mysql

解决方案

邻接表模型有局限性，在没有额外信息的情况下很难选择 mysql 中特定子树的所有叶子节点...

如果您有其他信息并且知道子树的高度 - 您可以执行以下操作：

SELECT t2.name as name
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
WHERE
    t1.name = 'TELEVISIONS' -- subtree
    AND t3.name IS NULL -- ensure it is leaf
;

但这是一个非常有限的例子，有很多缺点：你必须知道子树的高度，子树必须绝对平衡等等......
我宁愿建议你使用The Nested Set Model，在这种情况下查询会看起来

SELECT *
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = 'TELEVISIONS'
AND node.rgt = node.lft + 1
;

此外，它可以正常工作PORTABLE ELECTRONICS。

推荐阅读