sql 递归如何实现？就三个字段 100分！！！

Question

表 a 结构如下：id parentID sClassName
1  0        1111
2  1        1111_1
3  2        1111-1-1
4  3        1111-1-1-1
5  1        1111-2需求：用在sql语句里调一个递归函数 getParentID(id) 实现一次取得所有的父目录ID如：
set myID = getParentID(4) 那么 就会得 myID = '1,2,3'set myID = getParentID(3) 那么 就会得 myID = '1,2,3'set myID = getParentID(1) 那么 就会得 myID = '1'请麻烦给出详细程序。感谢！！！

Answer 1

给一个例子
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 a.*,b.name,b.id from employee a,
(select name,id from department where id = dbo.f_getrootid(2)) b
where a.id = 2
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          username did         name                 id          
----------- -------- ----------- -------------------- ----------- 
2           李四       4           销售部                  2（所影响的行数为 1 行）id          name                 num         
----------- -------------------- ----------- 
1           技术部                  3
2           销售部                  1（所影响的行数为 2 行）*/
godrop function f_getRootId
drop table department,employee
go

Answer 2

本帖最后由 libin_ftsafe 于 2007-11-29 14:52:35 编辑

  

Answer 3

--生成测试数据
create table BOM(ID INT,PID INT,sClassName VARCHAR(1000))
insert into BOM select 1,0,'1111 '
insert into BOM select 2,1,'1111_1'
insert into BOM select 3,2,'1111-1-1'
insert into BOM select 4,3,'1111-1-1-1'
insert into BOM select 5,1,'1111-2'
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns varchar(800)
as
begin
    declare @i int
    declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
    set @i = 1
    insert into @t select ID,PID,@i from BOM where PID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.PID=b.ID and b.Level = @i-1
    end
    declare @ret varchar(800)
    set @ret = ''
    select @ret = @ret + ltrim(id)+ ','
    from @t
    return @ret
end
go--执行查询
select ID,dbo.f_getChild(id) as result from bom
where id = 1
go--输出结果
/*
ID          result     
----------- --------
1           2,5,3,4,
*/--删除测试数据
drop function f_getChild
drop table BOM

Answer 4

下面是用的函数递规,但级数过多时可能超过32级嵌套,所以不推荐使用.
推荐使用循环,或者goto
CREATE TABLE tb(id INT,pid INT)
INSERT tb SELECT 1,     0
UNION ALL SELECT 2,     1
UNION ALL SELECT 3,     2
UNION ALL SELECT 4,     3
UNION ALL SELECT 5,     1
GO
CREATE FUNCTION getPidSTR
(
@id INT,
@s VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @oid INT
SET @oid=@id
SELECT @s=ISNULL((NULLIF(@s,'')+','),'') + RTRIM(id),@id=pid FROM tb a WHERE id=@oid
IF @id IS NOT NULL AND @id !=0
SELECT @s=dbo.getPidSTR(@id,@s)
RETURN @s
END
GOSELECT *,dbo.getPidSTR(id,'') FROM tb
GODROP TABLE tb
DROP FUNCTION getPidSTR
GO

Answer 5

--建立环境
create table tba (
id        int,
parentID  int, 
sClassName varchar(20)
)
insert tba select
1,     0,                 '1111'
union all select
2,     1,                 '1111_1' 
union all select
3,     2,                 '1111-1-1' 
union all select
4,     3,                 '1111-1-1-1' 
union all select
5,     1,                 '1111-2'--建立函数
create function fn_getParentID(@id int)
returns varchar(200)
as
begin
declare @r varchar(200)
set @r=''
declare @p int
select @p=parentID from tba where id=@id and parentID<>0
if @p is not null
begin
set @r=dbo.fn_getParentID(@p)
if  @r=''
set @r=cast(@p as varchar)
else
set @r=@r+','+cast(@p as varchar)
end
return @r
end
go--测试
select dbo.fn_getParentID(4)
 
--结果
                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1,2,3（所影响的行数为 1 行）

Answer 6

create function f_getRootId(@did int)
returns int
as
begin
declare @id int
     select @id = parentid   from  department where  id = @did
if(@id=0)
             return  @id
        else
             return  @id + ',' + f_getRootId(@id )
end

Answer 7

需不需要包括自身？
--包括自身
--建立环境
create table tba (
id        int,
parentID  int, 
sClassName varchar(20)
)
insert tba select
1,     0,                 '1111'
union all select
2,     1,                 '1111_1' 
union all select
3,     2,                 '1111-1-1' 
union all select
4,     3,                 '1111-1-1-1' 
union all select
5,     1,                 '1111-2'--建立函数
alter function fn_getParentID(@id int)
returns varchar(200)
as
begin
declare @r varchar(200)
set @r=''
declare @p int
select @p=parentID from tba where id=@id
if @p is not null
begin
set @r=dbo.fn_getParentID(@p)
if  @r=''
set @r=cast(@Id as varchar)
else
set @r=@r+','+cast(@Id as varchar)
end
return @r
end
go--测试
select dbo.fn_getParentID(1)
 
--结果
                                                                                                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1,2,3,4（所影响的行数为 1 行）

Answer 8

CREATE table a(id int,  parentID int,  sClassName nvarchar(20))
insert into a
select 1,     0,                 '1111' 
union all select 2,     1,                 '1111_1' 
union all select 3,     2,                 '1111-1-1' 
union all select 4,     3,                 '1111-1-1-1' 
union all select 5,     1,                 '1111-2'
create FUNCTION getParentID(@id INT)
RETURNS NVARCHAR(50)
AS
BEGIN
declare @parentid  int
DECLARE @allParentID NVARCHAR(50)
set @allParentID=''
select @parentid=[parentid] from a where id= @id
IF(@parentid <>0)
begin
  set @allParentID = cast(@parentid as nvarchar)  +','+  DBO.getParentID(@parentid)
end
return cast(@allParentID as nvarchar)
ENDSELECT  'ParentID'=dbo.getParentID(4)/*
ParentID
--------------------------------------------------
3,2,1,
*/

Answer 9

use test
go
create table BOM(ID INT,PID INT,sClassName VARCHAR(1000))
insert into BOM select 1,0,'1111 '
insert into BOM select 2,1,'1111_1'
insert into BOM select 3,2,'1111-1-1'
insert into BOM select 4,3,'1111-1-1-1'
insert into BOM select 5,1,'1111-2'gocreate function T_tree(@ID int)
returns nvarchar(100)
as
begin
declare @PID int
select @PID=PID from BOM where ID=@ID
if @PID is null or @PID=0
return null return isnull(dbo.T_tree(@PID)+',','')+rtrim(@PID) 
end
goselect dbo.T_tree(4)                                                                                                     
---------------------------------------------------------------------------------------------------- 
1,2,3（所影响的行数为 1 行）

Answer 10

use test
go
create table BOM(ID INT,PID INT,sClassName VARCHAR(1000))
insert into BOM select 1,0,'1111 '
insert into BOM select 2,1,'1111_1'
insert into BOM select 3,2,'1111-1-1'
insert into BOM select 4,3,'1111-1-1-1'
insert into BOM select 5,1,'1111-2'gocreate function T_tree(@ID int)
returns nvarchar(100)
as
begin
declare @PID int
select @PID=PID from BOM where ID=@ID
if @PID is null or @PID=0
return null return isnull(dbo.T_tree(@PID)+',','')+rtrim(@PID) 
end
goselect isnull(dbo.T_tree(ID),ID) from BOM---------------------------------------------------------------------------------------------------- 
1
1
1,2
1,2,3
1（所影响的行数为 5 行）drop function T_tree 
drop table BOM

Answer 11

create table a (id int,parentid int,classname varchar(20))
goinsert into a values(1, 0,'1111')
insert into a values (2, 1,'1111_1')
insert into a values (3,2,'1111-1-1')
insert into a values (4,3,'1111-1-1-1') 
insert into a values (5,1,'1111-2') 
go 
create function dbo.getParentID( @id int)
returns varchar(20)
as
begin
declare @res varchar(100),@parentid int
    if exists(select * from a where id=@id and parentid>0)
begin
select @parentid=parentid from a where id=@id
        set @res=dbo.getparentid(@parentid)+','+convert(varchar(20),@id)
end
    else
set @res=convert(varchar(20),@id)
return convert(varchar(20),@res)
end
go

Answer 12

create table teb(id int,parentid int,sclassname varchar(50))
insert into teb select 1,0,'1111'
insert into teb select 2,1,'1111-1'
insert into teb select 3,2,'1111-1-1'
insert into teb select 4,3,'1111-1-1-1'
insert into teb select 5,1,'1111-2'alter function ws(@id int)
returns varchar(50)
as
begin
declare @t table(id int,parentid int,lev int)
declare @i int
set @i=1
insert into @t select id,parentid,@i from teb where id in(select parentid from teb where id=@id)
while(@@rowcount>0)
begin
set @i=@i+1
insert into @t select b.id,b.parentid,@i from @t a,teb b where a.parentid=b.id and a.lev=@i-1
end
declare @sql varchar(50)
select @sql=isnull(@sql+',','')+cast(id as varchar) from @t order by id
return @sql
endselect dbo.ws(4)

Answer 13

set nocount on
go
--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111'      )
insert into BOM values(2,1,'1111_1'    )
insert into BOM values(3,2,'1111-1-1'  )
insert into BOM values(4,3,'1111-1-1-1') 
insert into BOM values(5,1,'1111-2'    )
go
create function fn_path( @id int ) -- 查询的参数
returns varchar(32) as begin
declare @pid int set @pid = 0
declare @path varchar(32) 

select @pid=parentID from BOM where ID=@id
while (@pid>0) begin
set @path=cast(@pid as varchar)+isnull(','+@path,'')
set @id = @pid
select @pid=parentID from BOM where ID=@id
end
return @path
end
goselect *,path=dbo.fn_path(id) from BOM
godrop function fn_path
go
drop table BOM
go