请教递归问题 大家都来帮忙看看 谢谢！

[dunerunner]

想了一下午不知道何从下手请问应该怎么递归啊？
谢谢！

假设表结构如下：

部门表(dept)
dept_id         dept_name        parent_dept_id
   1              部门A               null
   2              部门B                1
   3              部门C                1
   4              部门D                2

员工表(emp)
dept_id 	     emp_name
   1             赵1
   1             赵2
   1             赵3
   2             钱1
   2             钱2
   2             钱3
   3             孙1
   3             孙2
   4             李1
   4             李2
   4             李3

根据部门id查询统计当前部门直属员工数量
以及直属部门下员工以及直属部门下子部门下所有员工……

当根据部门A的id查询时 结果如下:
dept_name	      emp_count
  部门A            3
  部门B            6
  部门C            2

当根据部门B的id查询时 结果如下:
dept_name      emp_count
  部门B            3
  部门D            3

[dunerunner]

深度未知

[AcHerat]

-- 使用函数的方法：

--建立 演示环境

if object_id('tb_bookInfo') is not null drop table tb_bookInfo
go
create table tb_bookInfo(number int,name varchar(10),type int)
insert tb_bookInfo
select 1 ,'n1', 6 union all
select 2 ,'n2', 3


if object_id('tb_bookType') is not null drop table tb_bookType
go
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英语',0 union all
select 2,'生物',0 union all
select 3,'计算机',0 union all
select 4,'口语',1 union all
select 5,'听力',1 union all
select 6,'数据库',3 union all
select 7,'软件工程',3 union all
select 8,'SQL Server',6

select a.*,b.level from tb_bookInfo  a,f_getC(3) b  where a.type=b.id  order by b.level 
/*
number      name       type        level      
----------- ---------- ----------- -----------
2           n2         3           0
1           n1         6           1

（所影响的行数为 2 行）
*/
--查所有父结点
if object_id('f_getP') is not null drop function f_getP
go
create function f_getP(@id int) 
returns @re table(id int,level int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level
    return 
end 
go 


--查所有子结点
if object_id('f_getC') is not null drop function f_getC
go
create function f_getC(@id int) 
returns @re table(id int,level int) 
as 
begin
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return 
end 
go 

--查所有父子结点
if object_id('f_getAll') is not null drop function f_getAll
go
create function f_getAll(@id int) 
returns @re table(id int,level int) 
as 
begin 
    declare @l int 
    set @l=0 
    insert @re select @id,@l 
    while @@rowcount>0 
    begin 
 set @l=@l+1
 insert @re select a.parentid,@l from tb_bookType a,@re b
 where a.id=b.id and b.level=@l-1 and a.parentid<>0
    end 
    update @re set level=@l-level 
    while @@rowcount>0
    begin 
        set @l=@l+1
        insert @re select a.id,@l from tb_bookType as a,@re as b 
 where b.id=a.parentid and b.level=@l-1
    end
    return
end 
go  
 

--删除演示

drop table tb_bookInfo

drop table tb_bookType

drop function f_getP

drop function f_getC
drop function f_getAll
GO

--sqlserver2005的新方法

-- 建立演示环境
IF OBJECT_ID('[Dept]') IS NOT NULL
    DROP TABLE [Dept]
GO
CREATE TABLE Dept(
 id int PRIMARY KEY,
 parent_id int,
 name nvarchar(20))
INSERT Dept
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
--1、父-〉子
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
 -- 定位点成员
 SELECT * FROM Dept WHERE name = @Dept_name
 UNION ALL
 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
 SELECT A.*  FROM Dept A, DEPTS B  WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO
--结果如下
/*
id          parent_id   name                
----------- ----------- --------------------
6           4           MIS
7           6           UI
8           6           软件开发
9           8           内部开发

（所影响的行数为 4 行）
*/

--2、子-〉父
-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'内部开发'
;WITH
DEPTS AS(
 -- 定位点成员
  SELECT * FROM Dept WHERE name = @Dept_name
--SELECT d.id,d.parent_id,d.name,convert(nvarchar(50),d.name) as parent  FROM Dept where @Dept_name
 UNION ALL
 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
 SELECT a.* FROM Dept a, DEPTS b WHERE a.id = b.parent_id
)
SELECT * FROM DEPTS
GO

--结果如下
/*
id          parent_id   name                
----------- ----------- --------------------
9           8           内部开发
8           6           软件开发
6           4           MIS
4           0           业务部

（所影响的行数为 4 行）
*/

-- 删除演示环境
DROP TABLE Dept

[AcHerat]

--SQL2000楼主就用函数来解决，这里写个2005的，没测试。

;with ach as
(
    select dept_id as id,dept_id,parent_dept_id from dept
    union all
    select b.dept_id as id,a.dept_id,a.parent_dept_id
    from dept a join ach b on a.parent_dept_id = b.dept_id
)

select a.id,b.dept_name,count(*) as emp_count
from ach a join dept b on a.id = b.dept_id
    join emp c on a.dept_id = c.dept_id
group by a.id,b.dept_name

[飘零一叶]

create table dept
(
dept_id int,        
dept_name nvarchar(10),      
parent_dept_id int
)

create table emp
(
dept_id  int,        
emp_name nvarchar(10)
)

insert into dept
select 1, '部门A',null union all
select 2, '部门B',1 union all
select 3, '部门C',1 union all
select 4, '部门D',2

insert into emp
select 1,'赵1' union all
select 1,'赵2' union all
select 1,'赵3' union all
select 2,'钱1' union all
select 2,'钱2' union all
select 2,'钱3' union all
select 3,'孙1' union all
select 3,'孙2' union all
select 4,'李1' union all
select 4,'李2' union all
select 4,'李3'

create proc proc_dept_emp_count
@dept_name nvarchar(10)
as
begin
with cte as
(
  select * from dept where dept_name=@dept_name
  union all
  select b.* from cte a join dept b on a.dept_id=b.parent_dept_id
)
select dept_id
,dept_name
,(select COUNT(*) from emp where emp.dept_id=cte.dept_id) as num from cte 
end

exec proc_dept_emp_count '部门A'
/*
dept_id	dept_name	num
1	部门A	3
2	部门B	3
3	部门C	2
4	部门D	3
*/

exec proc_dept_emp_count '部门B'
/*
dept_id	dept_name	num
2	部门B	3
4	部门D	3
*/

[快溜]

select a.dept_name,
       emp_count=(select count(*) from emp where dept_id=a.dept_id)
from dept a 
 where dept_id in(select isnull(parent_dept_id,1) from dept where dept_id=@date_id)

[--小F--]

-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0  
insert into tb select 2,1  
insert into tb select 3,1  
insert into tb select 4,2  
insert into tb select 5,3  
insert into tb select 6,5  
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
   insert @t select ID from tb where ParentID=@ParentID
   while @@rowcount<>0
   begin
      insert @t select a.ID from tb a inner join @t b
      on a.ParentID=b.id and 
      not exists(select 1 from @t where id=a.id)
   end 
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
   insert @t select ParentID from tb where ID=@ID
   while @@rowcount!=0
   begin
     insert @t select a.ParentID from tb a inner join @t b
       on a.id=b.ParentID and 
       not exists(select 1 from @t where ParentID=a.ParentID)
   end
  return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/30/4619995.aspx