怎样查询树结构

Question

id  parent  name
1   0       node1
2   1       node2
3   1       node3
4   0       node4
5   2       node5
..怎么样查询
可以得到结果如下
|--node1
|----node2
|------node5
|----node3
|-node4
..多谢

Answer 1

贴一段小梁同学写的代码给你参考一下mysql> select * from tb_test;
+------------+--------------+--------------+
| ProdCateID | ProdCateName | ParentCateID |
+------------+--------------+--------------+
|          1 | 服装            |            0 |
|          2 | 箱包             |            0 |
|          3 | 内衣             |            1 |
|          4 | 外套             |            1 |
|          5 | 男箱包             |            2 |
|          6 | 女箱包            |            2 |
|          7 | 内裤            |            3 |
|          8 | 文胸             |            3 |
|          9 | 男外套             |            4 |
|         10 | 女大衣            |            4 |
|         11 | 男用钱包            |            5 |
|         12 | 女用钱包           |            6 |
+------------+--------------+--------------+
SP代码如下:
DELIMITER $$DROP PROCEDURE IF EXISTS `tennis`.`sp_tree_test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_tree_test`(in parent_id int)
begin
    declare level smallint default 0;
    declare cnt int default 0;
    create temporary table tt(ProdCateID int,ProdCateName varchar(20),
                              ParentCateID int,level smallint,sort varchar(1000));
    create temporary table tt2(ProdCateID int,ProdCateName varchar(20),
                              ParentCateID int,level smallint,sort varchar(1000));    insert into tt select ProdCateID,ProdCateName,
                          ParentCateID,level,cast(ProdCateID as char)
                   from tb_test
                   where ParentCateID=parent_id;    select row_count() into cnt;
    insert into tt2 select * from tt;    while cnt>0 do
        set level=level+1;
        truncate table tt;
        insert into tt select a.ProdCateID,a.ProdCateName,
                              a.ParentCateID,level,concat(b.sort,a.ProdCateID)
                       from tb_test a,tt2 b
                       where a.ParentCateID=b.ProdCateID and b.level=level-1;
        select row_count() into cnt;
        insert into tt2 select * from tt;
    end while;
    select ProdCateID,
           concat(space(a.level*2),'|--',a.ProdCateName) ProdCateName
    from tt2 a
    order by sort;    drop table tt;
    drop table tt2;
end $$DELIMITER ;##执行mysql> call sp_tree_test(0);
+------------+-----------------+
| ProdCateID | ProdCateName    |
+------------+-----------------+
|          1 | |--服装            |
|          3 |   |--内衣           |
|          7 |     |--内裤        |
|          8 |     |--文胸         |
|          4 |   |--外套           |
|         10 |     |--女大衣        |
|          9 |     |--男外套         |
|          2 | |--箱包             |
|          5 |   |--男箱包           |
|         11 |     |--男用钱包        |
|          6 |   |--女箱包          |
|         12 |     |--女用钱包       |
+------------+-----------------+
12 rows in set (0.30 sec)

Answer 2

http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
MySQL中进行树所有子节点的查询 

Answer 3

方法二：利用临时表和过程递归
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE showChildLst (IN rootId INT)
    -> BEGIN
    ->  CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst (sno int primary key auto_in
crement,id int,depth int);
    ->  DELETE FROM tmpLst;
    ->
    ->  CALL createChildLst(rootId,0);
    ->
    ->  select concat('|',REPEAT('-', tmpLst.depth),table1.name)
    ->          from tmpLst,table1
    ->          where tmpLst.id=table1.id
    ->          order by tmpLst.sno;
    ->
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)mysql> # 递归过程
mysql> CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)
    -> BEGIN
    ->  DECLARE done INT DEFAULT 0;
    ->  DECLARE b INT;
    ->  DECLARE cur1 CURSOR FOR SELECT id FROM table1 where parent=rootId;
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->  insert into tmpLst values (null,rootId,nDepth);
    ->
    ->  OPEN cur1;
    ->
    ->  FETCH cur1 INTO b;
    ->  WHILE done=0 DO
    ->          CALL createChildLst(b,nDepth+1);
    ->          FETCH cur1 INTO b;
    ->  END WHILE;
    ->
    ->  CLOSE cur1;
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)mysql>
mysql> delimiter ;
mysql> select * from table1;
+----+--------+-------+
| id | parent | name  |
+----+--------+-------+
|  1 |      0 | node1 |
|  2 |      1 | node2 |
|  3 |      1 | node3 |
|  4 |      0 | node4 |
|  5 |      2 | node5 |
+----+--------+-------+
5 rows in set (0.01 sec)mysql> set max_sp_recursion_depth=12;
Query OK, 0 rows affected (0.05 sec)mysql> call showChildLst(0);
+---------------------------------------------------+
| concat('|',REPEAT('-', tmpLst.depth),table1.name) |
+---------------------------------------------------+
| |-node1                                           |
| |--node2                                          |
| |---node5                                         |
| |--node3                                          |
| |-node4                                           |
+---------------------------------------------------+
5 rows in set (0.13 sec)Query OK, 0 rows affected (0.14 sec)mysql>


Answer 4

呵呵 我今早想到用两张表了一开始用 一张临时表   insert into  select 。。  同一张表的时候 出现了 锁定