求sql递归求人员的一级部门信息

Question

create table department(id int,name varchar(20),parentid int)
insert into department select 1,'技术部           ',0
insert into department select 2,'销售部           ',0
insert into department select 3,'上海技术部       ',1
insert into department select 4,'上海技术部-网络组',3create table employee(id int,username varchar(8),did int)
insert into employee select 1,'张三',1
insert into employee select 2,'李四',4
insert into employee select 3,'王五',2
insert into employee select 4,'马六',3
gocreate function f_getRootId(@did int)
returns int
as
begin
    while exists(select 1 from department where id=@did and parentid!=0)
        select @did=parentid from department where id=@did
    
    return @did
end
goselect 
    b.id,b.name,count(a.id) num 
from 
    (select dbo.f_getRootId(did) as id from employee) a,department b 
where 
    a.id=b.id 
group by 
    b.id,b.name/*
id          name                 num         
----------- -------------------- ----------- 
1           技术部                  3
2           销售部                  1
*/
godrop function f_getRootId
drop table department,employee
go

Answer 1

在SQL Server 2000里，最常见的解决之一：借助用户定义函数实现递归查询寻找ROOT

Answer 2

问题1:写个函数就可以了
create function fn_一级部门 (
@id int
)
returns int
as
begin
  declare @r int
  select @r=did from 人员表 where id=@id
  while exists (select 1 from 部门表 where id=@r and parentid<>0)
     select @r=parentid from 部门表 where id=@r and parentid<>0
  return @r
end
go--获得结果方法：
select a.id,a.username,b.name,b.id from 人员表 a,部门表 b
where a.id=2 
and b.id=dbo.fn_一级部门(2)

Answer 3

问题2:
select a.id,b.name,count(b.id)
from 部门表 a,人员表 b
where a.id=dbo.fn_一级部门(b.id)

Answer 4

问题2、求一级部门列表，并且列出部门所有人数，sql怎么写？
例
输出结果：
1   技术部    3（人数）
2   销售部    1可以参考以下函数：
/*
-----------------
EmployeeMsg 人事表名
IsDimission=79表示在职
-----------------
DataDepartment 部门表名
@DepartmentID 部门ID
Superior 上级部门ID
*/
--按部門統計人數,返回该部门的所有在职人数
CREATE FUNCTION CountEmployeeByDepartment(@DepartmentID int)
RETURNS int AS  
BEGIN 
DECLARE @Return int
IF EXISTS(SELECT 1 FROM DataDepartment WHERE Superior=@DepartmentID) 
BEGIN
SELECT @Return=ISNULL(@Return,0)+dbo.CountEmployeeByDepartment(DepartmentID) FROM DataDepartment WHERE Superior=@DepartmentID
SELECT @Return=@Return+ISNULL(COUNT(1),0) FROM EmployeeMsg WHERE Department=@DepartmentID AND IsDimission=79
END
ELSE
SELECT @Return=ISNULL(COUNT(1),0) FROM EmployeeMsg WHERE Department=@DepartmentID AND IsDimission=79
RETURN @Return
END