求Mysql查询语句

Question

有表如下：
id     fid
1      0
2      1
3      2
4      3
第二行记录的fid是第一条记录的id,依次类推。问题：查询fid等于0的记录的所有子记录（比如，上面列出的id为2、3、4的记录都是id为1的记录的子记录）,及所有子记录中最后的一条记录。

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

偶不敢呀.那些都是以前刚学的时候写的,
在SQL Server很容易能写这些,但在MySQL总是提示Table can't reopen..
所以只能用多个临时表来写..感觉不是很好..