mysql 层次数据_MySQL层次结构数据提取

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

I have a table like this:

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

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

Or

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

Or

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 :(

James

解决方案

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.