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

[sun2828]

表结构：
部门表：
id（部门id） name（部门名称） parentid（部门上级部门ID，0为一级部门）
1 技术部 0
2 销售部 0
3 上海技术部 1
4 上海技术部-网络组 3
......

人员表：
id username did（最小等级部门ID）
1 张三 1
2 李四 4
3 王五 2
4 马六 3
......


问题1、现在要根据人的ID（比如李四或马六的ID）求他所在一级部门，部门分级不局限于三级，可能更多，用递归怎么做sql语句？
例，李四ID=2
获得结果：2 李四 技术部 1（技术部ID）
马六ID=4
获得结果：4 马六 技术部 1


问题2、求一级部门列表，并且列出部门所有人数，sql怎么写？
例
输出结果：
1 技术部 3（人数）
2 销售部 1


求高手指教，问题解决马上结贴！

[li_d_s]

mark一下，是要一句sql解决吗？

[子陌红尘]

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,'上海技术部-网络组',3

create 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
go

create 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
go

select
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
*/
go

drop function f_getRootId
drop table department,employee
go

[子陌红尘]

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

[Yang_]

问题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)

[Yang_]

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

[-狙击手-]

钻石

[li_d_s]

我的娘诶，钻石是多少分啊？

[Andy-W]

问题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