mysql数据库的查询,大手们进来帮帮我啊。。困扰好几天了。

Question

数据库表包括有            推荐人字段     用户ID
            我推荐了 A  ， A推荐了B ，   B推荐了C 。 则我的下属人中就有  A,B,C 
                                                        A的下属人中就有   B，C
                                                         ..................... 但是在 mysql中怎么查询我的所有下属人呢。。

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)==================================-- ----------------------------
-- Table structure for tbtype
-- ----------------------------
DROP TABLE IF EXISTS `tbtype`;
CREATE TABLE `tbtype` (
  `typeID` int(11) NOT NULL auto_increment,
  `typeName` varchar(20) default NULL ,
  `typeParent` int(11) NOT NULL,
  PRIMARY KEY  (`typeID`)
) ENGINE=InnoDB AUTO_INCREMENT=140 DEFAULT CHARSET=gbk;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tbtype` VALUES ('1', '中国', '0');
INSERT INTO `tbtype` VALUES ('131', '江苏', '1');
INSERT INTO `tbtype` VALUES ('132', '湖北', '1');
INSERT INTO `tbtype` VALUES ('133', '南京', '131');
INSERT INTO `tbtype` VALUES ('134', '无锡', '131');
INSERT INTO `tbtype` VALUES ('135', '武汉', '132');
INSERT INTO `tbtype` VALUES ('136', '南长区', '134');
INSERT INTO `tbtype` VALUES ('137', '新区', '134');
INSERT INTO `tbtype` VALUES ('138', '武昌区', '135');
INSERT INTO `tbtype` VALUES ('139', '江夏区', '135'); delimiter $$drop procedure if exists GetTreeChildren $$create procedure GetTreeChildren(in_typeID INT)
begin
    declare l int default 1;
    declare cnt int default 0;
    create temporary table result(typeID int,typeParent int,typeName varchar(20),level int) engine=memory;
    create temporary table tmp(typeID int,level int) engine=memory;    insert into result
        select typeID,typeParent,typeName,l from tbtype where typeID=in_typeID;
    
    insert into tmp select typeID,level from result;    set cnt=row_count();
    
    while(cnt>0) do
        set l=l+1;        insert into result
            select
                a.typeID,a.typeParent,a.typename,
                l
            from tbtype as a
               join tmp as b
                  on a.typeParent=b.typeID
                      and b.level=l-1;
 
         
         set cnt=row_count();
         insert tmp select typeID,level from result where level=l;
    end while;    select * from result;    drop table result,tmp;
end$$delimiter ;

Answer 2

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