学步园

函数

CREATE FUNCTION GetChildren (@id varchar(20))
RETURNS @t table(id varchar(20))  AS 
BEGIN
    insert @t select subid from tree where parentid = @id
    while @@rowcount > 0
        insert @t select a.subid from tree as a inner join @t as b
        on a.parentid = b.id and a.subid not in(select id from @t)
   return
END

 

函数 GetChildren 获取子节点
函数 GetParent 获取父节点

 

select top 8 * from Pdt_Detail where CompanySortId = 1 or CompanySortId in (select * from GetChildren(1))

select * from Pdt_Detail where CompanySortId in(select * from GetChildren(38))