要实现的功能如下:
有一个表,存有id和父id,已知层次最多不超过四层,
ID NAME PARENT_ID
10 AAAA -1
20 BBBB -1
30 CCCC -1
101 DDDD 10
102 EEEE 10
103 FFFF 10
1011 GGGG 101
1012 HHHH 101
1021 IIII 102
201 JJJJ 20
2011 KKKK 201
...求一查询sql,得到如下结果:
ID NAME
10 AAAA
101 DDDD
1011 GGGG
1012 HHHH
102 EEEE
1021 IIII
103 FFFF
20 BBBB
201 JJJJ
2011 KKKK
30 CCCC就是得到一个树形结构的顺序。
有一个表,存有id和父id,已知层次最多不超过四层,
ID NAME PARENT_ID
10 AAAA -1
20 BBBB -1
30 CCCC -1
101 DDDD 10
102 EEEE 10
103 FFFF 10
1011 GGGG 101
1012 HHHH 101
1021 IIII 102
201 JJJJ 20
2011 KKKK 201
...求一查询sql,得到如下结果:
ID NAME
10 AAAA
101 DDDD
1011 GGGG
1012 HHHH
102 EEEE
1021 IIII
103 FFFF
20 BBBB
201 JJJJ
2011 KKKK
30 CCCC就是得到一个树形结构的顺序。
+------+------+-----------+
| ID | NAME | PARENT_ID |
+------+------+-----------+
| 10 | AAAA | -1 |
| 20 | BBBB | -1 |
| 30 | CCCC | -1 |
| 101 | DDDD | 10 |
| 102 | EEEE | 10 |
| 103 | FFFF | 10 |
| 1011 | GGGG | 101 |
| 1012 | HHHH | 101 |
| 1021 | IIII | 102 |
| 201 | JJJJ | 20 |
| 2011 | KKKK | 201 |
+------+------+-----------+
11 rows in set (0.00 sec)mysql> SELECT * FROM ttl1 a ORDER BY LEFT(CONCAT(a.id,'0000'),4);
+------+------+-----------+
| ID | NAME | PARENT_ID |
+------+------+-----------+
| 10 | AAAA | -1 |
| 101 | DDDD | 10 |
| 1011 | GGGG | 101 |
| 1012 | HHHH | 101 |
| 102 | EEEE | 10 |
| 1021 | IIII | 102 |
| 103 | FFFF | 10 |
| 20 | BBBB | -1 |
| 201 | JJJJ | 20 |
| 2011 | KKKK | 201 |
| 30 | CCCC | -1 |
+------+------+-----------+
11 rows in set (0.00 sec)mysql>
能不能根据父id来查询出想要的结果呢
http://blog.csdn.net/acmain_chm/article/details/4142971