MySQL层次结构数据提取 [英] MySQL Hierarchical Structure Data Extraction

问题描述

我已经在一个查询上苦苦挣扎了大约2个小时.帮助? :(

I've been struggling for about 2 hours on one query now. Help? :(

我有一个这样的表:

id    name                     lft      rgt        
35    Top level board          1        16     
37    2nd level board 3        6        15     
38    2nd level board 2        4        5     
39    2nd level board 1        2        3     
40    3rd level board 1        13       14     
41    3rd level board 2        9        12     
42    3rd level board 3        7        8     
43    4th level board 1        10       11

它存储在本教程中推荐的结构中.我要执行的操作是选择一个论坛板，然后在所选论坛板下面选择所有一级子论坛(不低).理想情况下，该查询将在仅通过董事会ID的同时获得所选论坛的级别，然后将选择该论坛及其所有直接子级.

It is stored in the structure recommended in this tutorial. What I want to do is select a forum board and all sub forums ONE level below the selected forum board (no lower). Ideally, the query would get the selected forum's level while only being passed the board's ID, then it would select that forum, and all it's immediate children.

所以，我希望最终会得到:

So, I would hopefully end up with:

id    name                     lft      rgt        
35    Top level board          1        16  

37    2nd level board 3        6        15     
38    2nd level board 2        4        5     
39    2nd level board 1        2        3     

或

id    name                     lft      rgt            
37    2nd level board 3        6        15  

40    3rd level board 1        13       14     
41    3rd level board 2        9        12     
42    3rd level board 3        7        8     

此处的前几行是父论坛，其他子论坛.另外，我想要一个给定深度值的东西，该深度是相对于所选父窗体的深度.例如，将最后一张表作为一些工作数据，我们将拥有:

The top rows here are the parent forums, the others sub forums. Also, I'd like something where a depth value is given, where the depth is relative to the selected parent form. For example, taking the last table as some working data, we would have:

id    name                     lft      rgt      depth      
37    2nd level board 3        6        15       0

40    3rd level board 1        13       14       1
41    3rd level board 2        9        12       1
42    3rd level board 3        7        8        1

或

id    name                     lft      rgt     depth      
35    Top level board          1        16      0

37    2nd level board 3        6        15      1
38    2nd level board 2        4        5       1
39    2nd level board 1        2        3       1

我希望你能在这里流连忘返.

I hope you get my drift here.

有人可以帮忙吗?现在真的让我很烦:(

Can anyone help with this? It's really getting me annoyed now :(

詹姆斯

推荐答案

要考虑的另一件事是关系数据库确实不是存储分层数据的最理想，最自然的方法.像您这样的结构-本质上是一棵二叉树-可以用可以持久保存的XML blob表示，也可以作为对象存储在面向对象的数据库中.

One more thing to consider is that relational databases really are not the most optimal and natural way to store hierarchical data. A structure like you have here - a binary tree, essentially - would be much easier to represent with an XML blob that you can persist, or store as an object in an object-oriented database.

这篇关于MySQL层次结构数据提取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！