求助，如何得到树的某个中间节点的所有叶子节点

Question

--創建表環境
create table tb(id int identity(1,1) not null constraint PK_tb primary key clustered
 ,pid int,name varchar(20))
insert into tb
 select 0,'中國'
 union all select 0,'美國'
 union all select 0,'加拿大'
 union all select 1,'北京'
 union all select 1,'上海'
 union all select 1,'江蘇'
 union all select 6,'蘇州'
 union all select 7,'常熟'
 union all select 6,'南京'
 union all select 6,'無錫'
 union all select 2,'紐約'
 union all select 2,'三藩市'
go
--得到指定id的子id列表
create function f_getchildid(@id int)
returns @re table(id int)
as
begin
 insert into @re select id from tb where pid=@id
 while @@rowcount>0
  insert into @re select a.id 
   from tb a inner join @re b on a.pid=b.id
   where a.id not in(select id from @re)
 return
end
go
select * from f_getchildid(1)
--所影響的行數
/*
id
4
5
6
9
10
7
8
*/

Answer 1

A
      /       \
     11       12
    /  \     /  \
  111  112 121 122
--子结点为111，112，121，122--测试数据
create table tree(
id char(3),
pid char(3)
)
goinsert into tree
select 'A','top'
union
select 'B','top'
union
select 'C','top'
union
select 'D','top'
union
select '11','A'
union
select '12','A'
union
select '111','11'
union
select '112','11'
union
select '121','12'
union
select '122','12'
go --执行
declare @s_curid char(3)
set @s_curid = 'A'
select id,pid 
  into #leafage
  from tree 
  where pid = @s_curid
while exists(select * from #leafage a where exists(select * from tree b where b.pid = a.id))
  begin
    insert into #leafage
    select a.id ,a.pid
      from tree a join #leafage b on a.pid = b.id
    delete #leafage 
      where exists(select * from #leafage b where #leafage.id = b.pid)
  end
select * from #leafage--删除
drop table #leafage
drop table tree

Answer 2

create table #(Parent_id int,item_id  int )
insert into #(Parent_id,item_id) values(1,0)
insert into #(Parent_id,item_id) values(2,1)
insert into #(Parent_id,item_id) values(3,1)
insert into #(Parent_id,item_id) values(4,2)
insert into #(Parent_id,item_id) values(5,2)
insert into #(Parent_id,item_id) values(6,3)
insert into #(Parent_id,item_id) values(7,2)
insert into #(Parent_id,item_id) values(8,3)
insert into #(Parent_id,item_id) values(9,7)
insert into #(Parent_id,item_id) values(10,9)set nocount on
declare @ int,@i int
set @=2
declare @t table(Parent_id int,item_id  int,iv int)
declare @t1 table(Parent_id int,item_id  int)
insert into @t1
select * from  #  where item_id=2
set @i=1
while  exists(select TOP 1 * from @t1)
begin
 insert into @t(Parent_id ,item_id ,iv )
 select *,@i from @t1
 delete from  @t1
  insert into @t1    
  select * from  # where item_id  in (select Parent_id from @t where iv=@i)
  set @i=@i+1
end
select * from @t
drop table #
set nocount off

Answer 3

CrazyFor(蚂蚁) 
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.730694if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_GetSubClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_GetSubClass]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_GetTopClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_GetTopClass]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TreeClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TreeClass]
GOCREATE TABLE [dbo].[TreeClass] (
[TC_id] [int] IDENTITY (1, 1) NOT NULL ,
[TC_PID] [int] NOT NULL ,
[TC_OtherTypeID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[TC_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL 
) ON [PRIMARY]
GOALTER TABLE [dbo].[TreeClass] WITH NOCHECK ADD 
CONSTRAINT [PK_TreeClass] PRIMARY KEY  CLUSTERED 
(
[TC_id]
)  ON [PRIMARY] 
GOSET IDENTITY_INSERT TreeClass on
 Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (1, 0, '', '中国' )
 Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (2, 0, '', '美国' ) Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (3, 0, '', '加拿大' )
 
 Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (4, 1, '', '北京' )
 
 Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (5, 1, '', '上海' )
 
 Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (6, 1, '', '江苏' )  Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (7, 6, '', '苏州' )  Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (8, 7, '', '常熟' )  Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (9, 6, '', '南京' )  Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (10, 6, '', '无锡' )
 
 Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (11, 2, '', '纽约' )  Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (12, 2, '', '旧金山' ) SET IDENTITY_INSERT TreeClass offGo
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GOCREATE FUNCTION FN_GetSubClass (@InputId int,@IdStr varchar(8000))  RETURNS Varchar(8000)
 AS  
BEGIN Declare @TC_ID int,@TC_PID intIf @IdStr='' Set @IdStr=''''+cast(@InputId as varchar)+''''DECLARE TreeClass CURSOR local FOR 
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_PID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@FETCH_STATUS = 0
BEGIN
select @IdStr=@IdStr+','+''''+cast(@tC_ID as varchar)+'''' select @IdStr=dbo.FN_GetSubClass (@TC_ID,@IdStr)
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClassReturn @IdStrEND
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GOSET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GOCREATE FUNCTION FN_GetTopClass (@InputId int,@IdStr varchar(8000),@type int)  RETURNS Varchar(8000)
 AS  
BEGIN Declare @TC_ID int,@TC_PID intDECLARE TreeClass CURSOR local FOR 
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputIdOPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PIDWHILE @@FETCH_STATUS = 0
BEGIN
if @type=1
begin
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+cast(@tC_ID as varchar)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar) select @IdStr=dbo.FN_GetTopClass (@TC_PID,@IdStr,@type)
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PIDEndCLOSE TreeClass
DEALLOCATE TreeClassReturn @IdStrEND
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO例：
--得到ID为1的所有下层类别ID串
select dbo.fn_getsubclass(1,'')  
--查询ID为1的所有下层记录
select * from treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_getsubclass(1,''))>0
--得到ID为10顶层ID
select dbo.fn_gettopclass(10,'',0)
--得到提供ID所在枝的所有ID
select dbo.fn_getsubclass(dbo.fn_gettopclass(10,'',0),'')
--得到当前ID到顶层的ID串
select dbo.fn_gettopclass(10,'',1)

Answer 4

sky_blue(老衲) 
create table new_tree  ( parent varchar(80), child varchar(80),weight int)
go
insert new_tree values ( '1','2',3);
insert new_tree values ( '1','3',4);
insert new_tree values ( '2','4',3);
insert new_tree values ( '2','5',5);
insert new_tree values ( '3','6',7);
insert new_tree values ( '3','7',3);
insert new_tree values ( '3','8',2);
insert new_tree values ( '6','9',1);
insert new_tree values ( '5','10',4);
insert new_tree values ( '4','11',3);
insert new_tree values ( '9','12',7);
insert new_tree values ( '7',NULL,1);
insert new_tree values ( '8',NULL,4);
insert new_tree values ( '10',NULL,3);
insert new_tree values ( '11',NULL,3);
insert new_tree values ( '12',NULL,7);广度优先：
create proc proc_new_tree (@parent varchar(80),@mode int =0)
asbegin
  set nocount on
  declare @level int  
  declare @tmp1  table ( parent varchar(80), child varchar(80),level int)
  select @level =1
  insert @tmp1 select parent,child,@level from new_tree where parent = @parent
  while exists(select * from @tmp1 where child is not NULL and level=@level)
  begin
    insert @tmp1 select a.parent,a.child ,@level+1  from new_tree a,@tmp1 b where a.parent = b.child and b.level=@level
    select @level=@level +1
  end
  if @mode =0 select * from @tmp1
  else select * from @tmp1 where child is null
  set nocount off
end
go深度优先：
create proc proc__tree (@parent char(20))
 as
begin
  set nocount on
  declare @level int ,@i int ,@flag int
  declare @stack table (parent char(20),child char(20),level int,row int , flag int)
  select @level = 1,@i=1,@flag=1
  insert @stack select parent,child, @level,0,1 from   new_tree  where parent = @parent and child is not null
  
  while @level > 0
  begin
    if exists (select * from @stack where level = @level and flag=1)
    begin
       select @parent = min(child) from @stack  where level = @level and flag=1
       update @stack set flag =0 , row=@i where level = @level and child = @parent and  flag =1
       select @i = @i +1
       insert @stack select parent,child, @level + 1,0,1 from   new_tree  where parent = @parent and child is not null
       if @@rowcount > 0
          select @level = @level + 1
    end
    else
    begin
      select @level = @level - 1
    end
  end 
  select row,parent ,child,level from @stack order by row
  set nocount off
end
go
proc__tree '1'

Answer 5

http://blog.csdn.net/xluzhong/articles/340928.aspx

Answer 6

比如说树是这个样子的
      a
     /  \
    b    g
   / \
  c   d
      /\
     e  f
那么找b的叶节点，结果应该是c,e,f

Answer 7

--示例数据
create table tb(id int,pid int,name varchar(20))
insert tb select 1,0,'a'
union all select 2,1,'b'
union all select 3,1,'g'
union all select 4,2,'c'
union all select 5,2,'d'
union all select 6,5,'e'
union all select 7,5,'f'
go--查询处理函数
create function f_cid(
@name varchar(10)
)returns @re table(id int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select a.id,@l
from tb a,tb b
where a.pid=b.id and b.name=@name
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l
from tb a,@re b
where a.pid=b.id and b.[level]=@l-1
end
delete a from @re a
where exists(
select 1 from tb where pid=a.id)
return
end
go--调用(查询所有的子)
select a.*,层次=b.[level] from tb a,f_cid('b')b where a.id=b.id
go--删除测试
drop table tb
drop function f_cid/*--结果
id          pid         name                 层次          
----------- ----------- -------------------- ----------- 
4           2           c                    0
6           5           e                    1
7           5           f                    1（所影响的行数为 3 行）
--*/

Answer 8

--示例数据
create table tree(id char(3),pid char(3))
insert tree select 'a','top'
union all select 'b','a'
union all select 'g','a'
union all select 'c','b'
union all select 'd','b'
union all select 'e','d'
union all select 'f','d'
go--执行
declare @s_curid char(3)
set @s_curid = 'b'
select id,pid 
  into #leafage
  from tree 
  where pid = @s_curid
while exists(select * from #leafage a where exists(select * from tree b where b.pid = a.id))
  begin
    insert into #leafage
    select a.id ,a.pid
      from tree a join #leafage b on a.pid = b.id
    delete #leafage 
      where exists(select * from #leafage b where #leafage.id = b.pid)
  end
select id from #leafage--删除
drop table #leafage
drop table tree