mysql递归查询。

[chinagavin]

mysql> select * from testdb;
+------+------+------+
| id | name | pid |
+------+------+------+
| 1 | a | 0 |
| 2 | a | 1 |
| 3 | b | 1 |
| 4 | b | 2 |
| 5 | c | 2 |
| 6 | c | 3 |
| 7 | c | 0 |
| 8 | d | 0 |
| 9 | e | 7 |
+------+------+------+
9 rows in set (0.00 sec)

这样的表结构，如何查询出id = 1的记录机器子记录。
谢谢。
就是一个mysql递归查询的过程。

[ACMAIN_CHM]

直接用SQL语句无法实现，可以利用存储过程（procedure 或 function )

mysql> create table testdb (
    ->  id      int primary key,
    ->  name    varchar(10),
    ->  pid     int
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql>
mysql> insert into testdb values
    -> (1 ,'a',0),
    -> (2 ,'a',1),
    -> (3 ,'b',1),
    -> (4 ,'b',2),
    -> (5 ,'c',2),
    -> (6 ,'c',3),
    -> (7 ,'c',0),
    -> (8 ,'d',0),
    -> (9 ,'e',7);
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> set global log_bin_trust_function_creators=on ;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql>
mysql> CREATE FUNCTION getChildLst(rootId INT)
    ->  RETURNS VARCHAR(1000)
    -> BEGIN
    ->   DECLARE sTemp VARCHAR(1000);
    ->   DECLARE sTempChd VARCHAR(1000);
    ->
    ->   SET sTemp = cast(rootId as CHAR);
    ->   SET sTempChd =sTemp;
    ->
    ->   WHILE sTempChd is not null DO
    ->     SELECT group_concat(id) INTO sTempChd FROM testdb where FIND_IN_SET(
id,sTempChd);
    ->     SET sTemp = concat(sTemp,',',sTempChd);
    ->   END WHILE;
    ->   RETURN sTemp;
    ->
    -> END;
    -> //
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> delimiter ;

mysql> select *
    -> from testdb
    -> where FIND_IN_SET(id,getChildLst(1));
+----+------+------+
| id | name | pid  |
+----+------+------+
|  1 | a    |    0 |
|  2 | a    |    1 |
|  3 | b    |    1 |
|  4 | b    |    2 |
|  5 | c    |    2 |
|  6 | c    |    3 |
+----+------+------+
6 rows in set (0.01 sec)

[chinagavin]

谢谢。不过存储过程我已经实现了。
看来mysql还是不如oracle提供的那个比较好用。

[坤霆]

请问楼主 存储过程如何实现？我也遇到这个问题，能贴出来么？谢谢。