MySQL嵌套集-如何查找节点的父级? [英] MySQL Nested Sets - How to find parent of node?

问题描述

我在以下几列中运行了磨机嵌套集层次结构类型设置:

I have your run of the mill nested set hierarchy type setup with the following columns:

表名:

myset

列:

id, name, lft, rgt

有人知道一个查询来确定节点的 parent 吗?

Does anyone know a query to determine the parent of a node?

我读了几个地方，在表中也有一个 parent_id 列可以很方便地进行跟踪，但是它看起来很多余，而且看起来可能与嵌套不同步设置在添加/删除/移动集合中的任何内容时是否错误执行了查询.

I read a couple places that it's handy to also have a parent_id column in your table to keep track of this, but it seems redundant and it seems like it could get out of sync with the nested set if a query was incorrectly executed when adding/removing/moving anything within the set.

推荐答案

查看

Look at this question. It is similar to yours. I have posted there a query you may need.

SELECT title, (SELECT TOP 1 title 
           FROM tree t2 
           WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt    
           ORDER BY t2.rgt-t1.rgt ASC) AS parent
FROM tree t1
ORDER BY rgt-lft DESC

我希望您有需要.

对于下表:

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

它产生输出:

title                | parent
----------------------------------------------
ELECTRONICS          | NULL
PORTABLE ELECTRONICS | ELECTRONICS
TELEVISIONS          | ELECTRONICS
MP3 PLAYERS          | PORTABLE ELECTRONICS
FLASH                | MP3 PLAYERS
CD PLAYERS           | PORTABLE ELECTRONICS
2 WAY RADIOS         | PORTABLE ELECTRONICS
TUBE                 | TELEVISIONS
LCD                  | TELEVISIONS
PLASMA               | TELEVISIONS

这篇关于MySQL嵌套集-如何查找节点的父级?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！