在MySQL数据库中,怎样处理树型结构

请教个问题：在MySQL数据库中，怎样查询一个部门的所有下级部门的人员呀?
具体情况是这样的:
一共两个表，一个user表，一个部门表，部门表里有一个部门id,还有一个上级部门id,比如当前是一个部长登陆，要把它的所有下属人员全查出来，应该怎样查，网上搜了，没搜到结果，请大家帮忙。(也不要考虑性能上的问题，这个东西就是主管为了考我们给我们弄了点难度，实际产品orcal的数型结构比mysql好用多了。)
具体表结构:
dept表:
  id(自增值)
  dept_cd(部门编号，唯一)
  dept_name(名称)
  upr_dept_cd(上级部门编号)
  manager_no(部门领导工号)

user表
  id(自增值)
  user_no(工号)
  dept_cd(所在部门编号)
其它省略了，全是个人的联系方式。怎样查询某个主管下属的所有人员呀

7 个解决方案

#1

select * from user where dept_cd in (
    select * from dept where upr_dept_cd=(
       select dept_cd from dept a,user b where a.dept_cd=b.dept_cd and b.user_no='领导工号'
    )
)

#2

修改下，上面的一个*改为dept_cd

select * from user where dept_cd in (
    select dept_cd from dept where upr_dept_cd=(
       select dept_cd from dept a,user b where a.dept_cd=b.dept_cd and b.user_no='领导工号'
    )
)

#3

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)

#4

-- ----------------------------
-- 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 ;

#5

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DateBind();
           
        }
    }
    /// <summary>
    /// 微软
    /// </summary>
    void DateBind()
    {
        DAL.Tables.Biao1 B1 = new DAL.Tables.Biao1();
        DataTable dt = B1.Open("OID,Prod_name","","");        
        foreach (DataRow dr in dt.Rows)
        {
            TreeNode tn = new TreeNode();
            tn.Text = dr["Prod_name"].ToString();
            tn.Value = dr["OID"].ToString();
            BateBindNode(tn);
            TreeView1.Nodes.Add(tn);
            
        }
    }
    void BateBindNode(TreeNode tn)
    {
        DAL.Tables.Biao2 B2 = new DAL.Tables.Biao2();
        DataTable da = B2.Open("name","OID="+int.Parse(tn.Value),"");
        foreach (DataRow dt in da.Rows)
        {
            TreeNode tno = new TreeNode();
            tno.Text = dt["name"].ToString();
            //tno.Value = dt["OID"];
            tn.ChildNodes.Add(tno);
            tno.NavigateUrl = "~/default.aspx";
        }
    }

#6

DAL.Tables.Biao1 B1 = new DAL.Tables.Biao1(); 
这是调用写的SQL语句方法。

#7

引用 2 楼 william3033 的回复:

修改下，上面的一个*改为dept_cd 

SQL code
select * from user where dept_cd in (
    select dept_cd from dept where upr_dept_cd=(
       select dept_cd from dept a,user b where a.dept_cd=b.dept_cd and b.user_no='领导工号'
    )
)

这个有问题，这个 SQL只是把直接下级人员查出来了，没有查出间接下级的，也就是没有递归。(我执行的结果为null，不知什么原因)