查询（递归查询）问题

Question

表的结构很简单，如下：id parentid thevalue
1 0 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 6
7 2 7
8 2 8
9 6 9
10 6 10
11 6 11
12 7 12
13 7 13
14 7 14
15 3 15
16 3 16
17 3 17我现在需要查找某个id的所有父记录以及某个id的所有子记录。

Answer 1

参考一下这个贴子。http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
MySQL中进行树状所有子节点的查询 

Answer 2

那就参考ACMAIN_CHM大哥的博客吧！

Answer 3

我还是比较喜欢记录一下每个节点的path来避免递归：
mysql> select * from t_cqlxm t;
+----+----------+----------+
| id | parentid | path     |
+----+----------+----------+
|  1 |        0 | 1        |
|  2 |        1 | 1,2      |
|  3 |        1 | 1,3      |
|  4 |        1 | 1,4      |
|  5 |        1 | 1,5      |
|  6 |        2 | 1,2,6    |
|  7 |        2 | 1,2,7    |
|  8 |        2 | 1,2,8    |
|  9 |        6 | 1,2,6,9  |
| 10 |        6 | 1,2,6,10 |
| 11 |        6 | 1,2,6,11 |
| 12 |        7 | 1,2,7,12 |
| 13 |        7 | 1,2,7,13 |
| 14 |        7 | 1,2,7,14 |
| 15 |        3 | 1,3,15   |
| 16 |        3 | 1,3,16   |
| 17 |        3 | 1,3,17   |
+----+----------+----------+
17 rows in set (0.00 sec)--本身及所有父节点
mysql> select t.* from t_cqlxm t,t_cqlxm x where x.id=6 and find_in_set(t.id,x.p
ath);
+----+----------+-------+
| id | parentid | path  |
+----+----------+-------+
|  1 |        0 | 1     |
|  2 |        1 | 1,2   |
|  6 |        2 | 1,2,6 |
+----+----------+-------+
3 rows in set (0.00 sec)--本身及所有子节点
mysql> select * from t_cqlxm t where find_in_set(2,path);
+----+----------+----------+
| id | parentid | path     |
+----+----------+----------+
|  2 |        1 | 1,2      |
|  6 |        2 | 1,2,6    |
|  7 |        2 | 1,2,7    |
|  8 |        2 | 1,2,8    |
|  9 |        6 | 1,2,6,9  |
| 10 |        6 | 1,2,6,10 |
| 11 |        6 | 1,2,6,11 |
| 12 |        7 | 1,2,7,12 |
| 13 |        7 | 1,2,7,13 |
| 14 |        7 | 1,2,7,14 |
+----+----------+----------+
10 rows in set (0.00 sec)

Answer 4

问题解决，参考自ACMAIN_CHM的回复