返回表中,节点及其子节点,叶子节点

use FERMS
if exists (select Name from sysobjects where name = 'F_ReturnChildNode' and type = 'TF')
    drop function dbo.F_ReturnChildNode
go
/*
F----获得子节点
    --------返回表中,RBSID为本节点及其子节点
    ----多个节点的子节点,单个节点的子节点
    select * from dbo.F_ReturnChildNode('3,4,5') order by RBS,level
*/

create function dbo.F_ReturnChildNode(@RBSID varchar(100))
    returns @t table(RBSID int,RBSChildID varchar(2000),level int,RBS int)
as

begin

    while charindex(',',@RBSID)>=0  --如果没有,取第一个;如果有',',循环
    begin
        declare @level int  --层级
        set @level=0

        insert into @t select RBSID,ParentRBSID,@level,left(@RBSID,1) from RM_RiskClass where RBSID=left(@RBSID,1) --根节点
        while @@rowcount > 0 --子节点
        begin
            set @level=@level+1
            insert into @t 
                select a.RBSID,a.ParentRBSID,@level,left(@RBSID,1) from RM_RiskClass a left join @t b on a.ParentRBSID=b.RBSID
                where b.Level=@level-1 and b.RBS=left(@RBSID,1)
        end
        set @RBSID=stuff(@RBSID,1,2,'')  --去掉前一个
    end

return
end
go

 

/*
declare @RBSID varchar(100)
set @RBSID='3,4,5,6'
select left(@RBSID,1)
while charindex(',',@RBSID)>0  --如果有','
begin
 set @RBSID=stuff(@RBSID,1,2,'')
 select left(@RBSID,1)
end
*/