php - MySQL - 如何访问表中级别 2 中的所有节点

Question

我正在为具有嵌套类别的电子商务网站创建数据库，并且我正在使用修改后的预购遍历算法。我的问题是如何访问第 2 级的所有节点，即 Articles、Portfolio、Contact

Answer 1

The article没有明确告诉您如何从一个级别获取所有节点。但如果你仔细阅读它，它会告诉你如何做更多 -> 获取每个类别的深度计数。然后您所要做的就是按该深度进行过滤。

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft;

编辑(发生了什么):

为了使用 nested_category 表的 lft 和 rgt 列，我们应该选择该表两次。

SELECT *
FROM nested_category AS node, nested_category AS parent

如果检查此查询，您会发现对于 nested_category 中的每一行，我们都会再次选择所有行。所以我们现在想要的是从第一个表(我们称为 AS 节点)中删除所有行，这些行不是它们的 parent 的子行。这就是我们使用 WHERE node.lft BETWEEN parent.lft AND parent.rgt

进行过滤的原因

我想提一下这个查询:

SELECT *
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
ORDER BY node.lft;

等于

SELECT *
FROM nested_category AS node
LEFT JOIN nested_category AS parent ON (node.lft BETWEEN parent.lft AND parent.rgt)
ORDER BY node.lft;

所以现在我们有所有 child 和他们的 parent + 1(由于我们的过滤方式，每个 child 都属于自己)

+-------------+----------------------+-----+-----+-------------+----------------------+------+------+
| category_id | name                 | lft | rgt | category_id | name                 | lft  | rgt  |
+-------------+----------------------+-----+-----+-------------+----------------------+------+------+
|           1 | ELECTRONICS          |   1 |  20 |           1 | ELECTRONICS          |    1 |   20 |
|           2 | TELEVISIONS          |   2 |   9 |           1 | ELECTRONICS          |    1 |   20 |
|           2 | TELEVISIONS          |   2 |   9 |           2 | TELEVISIONS          |    2 |    9 |
|           3 | TUBE                 |   3 |   4 |           1 | ELECTRONICS          |    1 |   20 |
|           3 | TUBE                 |   3 |   4 |           3 | TUBE                 |    3 |    4 |
|           3 | TUBE                 |   3 |   4 |           2 | TELEVISIONS          |    2 |    9 |
|           4 | LCD                  |   5 |   6 |           2 | TELEVISIONS          |    2 |    9 |
|           4 | LCD                  |   5 |   6 |           1 | ELECTRONICS          |    1 |   20 |
|           4 | LCD                  |   5 |   6 |           4 | LCD                  |    5 |    6 |
|           5 | PLASMA               |   7 |   8 |           1 | ELECTRONICS          |    1 |   20 |
|           5 | PLASMA               |   7 |   8 |           5 | PLASMA               |    7 |    8 |
|           5 | PLASMA               |   7 |   8 |           2 | TELEVISIONS          |    2 |    9 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |           1 | ELECTRONICS          |    1 |   20 |
|           7 | MP3 PLAYERS          |  11 |  14 |           7 | MP3 PLAYERS          |   11 |   14 |
|           7 | MP3 PLAYERS          |  11 |  14 |           1 | ELECTRONICS          |    1 |   20 |
|           7 | MP3 PLAYERS          |  11 |  14 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|           8 | FLASH                |  12 |  13 |           1 | ELECTRONICS          |    1 |   20 |
|           8 | FLASH                |  12 |  13 |           8 | FLASH                |   12 |   13 |
|           8 | FLASH                |  12 |  13 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|           8 | FLASH                |  12 |  13 |           7 | MP3 PLAYERS          |   11 |   14 |
|           9 | CD PLAYERS           |  15 |  16 |           1 | ELECTRONICS          |    1 |   20 |
|           9 | CD PLAYERS           |  15 |  16 |           9 | CD PLAYERS           |   15 |   16 |
|           9 | CD PLAYERS           |  15 |  16 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
|          10 | 2 WAY RADIOS         |  17 |  18 |           1 | ELECTRONICS          |    1 |   20 |
|          10 | 2 WAY RADIOS         |  17 |  18 |          10 | 2 WAY RADIOS         |   17 |   18 |
|          10 | 2 WAY RADIOS         |  17 |  18 |           6 | PORTABLE ELECTRONICS |   10 |   19 |
+-------------+----------------------+-----+-----+-------------+----------------------+------+------+

下一步 - 获取深度计数。为了做到这一点，我们必须按每个 child 分组(示例使用 GROUP BY node.name 但它也可以在 node.category_id 上完成并计算数量parents - 每组 1 个 (COUNT(parent.name) - 1) AS depth(也可以使用 parent.category_id )

这样做

SELECT node.*, (COUNT(parent.category_id) - 1) AS depth
FROM nested_category AS node
LEFT JOIN nested_category AS parent ON (node.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY node.category_id
ORDER BY node.lft;

我们明白了

+-------------+----------------------+-----+-----+-------+
| category_id | name                 | lft | rgt | depth |
+-------------+----------------------+-----+-----+-------+
|           1 | ELECTRONICS          |   1 |  20 |     0 |
|           2 | TELEVISIONS          |   2 |   9 |     1 |
|           3 | TUBE                 |   3 |   4 |     2 |
|           4 | LCD                  |   5 |   6 |     2 |
|           5 | PLASMA               |   7 |   8 |     2 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |     1 |
|           7 | MP3 PLAYERS          |  11 |  14 |     2 |
|           8 | FLASH                |  12 |  13 |     3 |
|           9 | CD PLAYERS           |  15 |  16 |     2 |
|          10 | 2 WAY RADIOS         |  17 |  18 |     2 |
+-------------+----------------------+-----+-----+-------+

现在是最后一步，我们只需要这些记录，深度 = 1(HAVING depth = 1。HAVING 在这里使用是因为它在聚合之后应用(因此它可以过滤聚合))

SELECT node.*, (COUNT(parent.category_id) - 1) AS depth
FROM nested_category AS node
LEFT JOIN nested_category AS parent ON (node.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY node.category_id
HAVING depth = 1
ORDER BY node.lft;

+-------------+----------------------+-----+-----+-------+
| category_id | name                 | lft | rgt | depth |
+-------------+----------------------+-----+-----+-------+
|           2 | TELEVISIONS          |   2 |   9 |     1 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |     1 |
+-------------+----------------------+-----+-----+-------+

我希望它现在更清楚了。如果我犯了一些错误，再次为我糟糕的英语感到抱歉。