sql: T-SQL parent-child function script

--Parent-Child reationship--涂聚文 2014-08-25--得位置的子節點函數表（包含本身）if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBookPlaceChildrenId]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[GetBookPlaceChildrenId]GOCreate Function GetBookPlaceChildrenId(@ID int)Returns @Tree Table (BookPlaceID Int,BookPlaceParent Int, BookPlaceName NVarchar(180))AsBeginInsert @Tree Select BookPlaceID,BookPlaceParent, BookPlaceName From BookPlaceList Where BookPlaceID = @IDWhile @@Rowcount > 0Insert @Tree Select A.BookPlaceID, A.BookPlaceParent, A.BookPlaceName From BookPlaceList A Inner Join @Tree B On A.BookPlaceParent = B.BookPlaceID And A.BookPlaceID Not In (Select BookPlaceID From @Tree)--- ReturnEndGOselect * from dbo.GetBookPlaceChildrenId (2)---得到位置子节点列表ID地址函數（包含本身）if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBookPlaceGroupId]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[GetBookPlaceGroupId]GOCreate Function [dbo].[GetBookPlaceGroupId](@BookPlaceID int)RETURNS NVARCHAR(200)ASBEGINdeclare @allstring nvarchar(200),@top nvarchar(200)--,@BookPlaceID int--set @BookPlaceID=2set @allstring='' select @allstring=@allstring+cast(BookPlaceID as varchar(10))+',' FROM [dbo].[GetBookPlaceChildrenId](@BookPlaceID) ORDER BY BookPlaceID  --where CompanyID<>@CompanyIDset  @allstring=LEFT(@allstring,LEN(@allstring)-1)--select @allstringRETURN @allstringENDGOselect  [dbo].[GetBookPlaceGroupId] (2)----查位置所有子结点,带路径与排序  if object_id('GetBookPlaceParentLevel') is not null drop function GetBookPlaceParentLevel  go  create function GetBookPlaceParentLevel(@id int)   returns @re table(BookPlaceID int,BookPlaceParent int,BookPlaceName nvarchar(100),[level] int,sort varchar(100),BookPlaceFullName nvarchar(500))   as   begin      declare @l int       set @l=0       insert @re   select BookPlaceID,BookPlaceParent,BookPlaceName,@l,right('000'+ltrim(BookPlaceID),3),BookPlaceName  from BookPlaceList where BookPlaceParent=@id      while @@rowcount>0      begin           set @l=@l+1          insert @re    select a.BookPlaceID,a.BookPlaceParent,a.BookPlaceName,@l,b.sort+right('000'+ltrim(a.BookPlaceID),3), b.BookPlaceFullName+''+a.BookPlaceName from BookPlaceList as a,@re as b     where b.BookPlaceID=a.BookPlaceParent and b.[level]=@l-1      end      update @re set [level] = [level]      return   end   go  select * from GetBookPlaceParentLevel(0)select * from GetBookPlaceParentLevel (0) where BookPlaceID<>1select * from GetBookPlaceParentLevel (0) where BookPlaceID<>1select * from GetBookPlaceParentLevel (0) where BookPlaceID<>1 and [level]=1 --測試結果/*21第一层楼1001002涂聚文图书位置目录第一层楼31第二层楼1001003涂聚文图书位置目录第二层楼42第一排2001002004涂聚文图书位置目录第一层楼第一排62第二排2001002006涂聚文图书位置目录第一层楼第二排74第二层3001002004007涂聚文图书位置目录第一层楼第一排第二层84第三层3001002004008涂聚文图书位置目录第一层楼第一排第三层54第一层3001002004005涂聚文图书位置目录第一层楼第一排第一层124第四层3001002004012涂聚文图书位置目录第一层楼第一排第四层96第一层3001002006009涂聚文图书位置目录第一层楼第二排第一层106第二层3001002006010涂聚文图书位置目录第一层楼第二排第二层116第三层3001002006011涂聚文图书位置目录第一层楼第二排第三层*/

declare @id intset @id = 3;with t as--如果CTE前面有语句，需要用分号隔断(select BookKindID, BookKindParent, BookKindNamefrom BookKindListwhere BookKindID = @idunion allselect r1.BookKindID,r1.BookKindParent,r1.BookKindNamefrom BookKindList r1 join t as r2 on r1.BookKindParent = r2.BookKindID)select * from t order by BookKindID-- 查找所有父节点with tab as( select BookKindID,BookKindParent,BookKindName from BookKindList where BookKindID=3--子节点 union all select b.BookKindID,b.BookKindParent,b.BookKindName  from  tab a,--子节点数据集  BookKindList b  --父节点数据集 where a.BookKindParent=b.BookKindID  --子节点数据集.parendID=父节点数据集.ID)select * from tab; -- 查找所有子节点with tab as( select BookKindID,BookKindParent,BookKindName from BookKindList where BookKindID=3--父节点 union all select b.BookKindID,b.BookKindParent,b.BookKindName  from  tab a,--父节点数据集  BookKindList b--子节点数据集  where b.BookKindParent=a.BookKindID  --子节点数据集.ID=父节点数据集.parendID)select * from tab;--查找从子节点到定级节点的路径with tab as( select BookKindID,BookKindParent,BookKindName,cast(BookKindID as varchar(100)) as fulltypeid from BookKindList where BookKindID=3--子节点 union all select   b.BookKindID,b.BookKindParent,b.BookKindName,   cast(a.fulltypeid+','+cast(b.BookKindID as nvarchar(100)) as varchar(100)) as fulltypeid from  tab a,--子节点数据集  BookKindList b  --父节点数据集 where a.BookKindParent=b.BookKindID  --子节点数据集.parendID=父节点数据集.ID)select * from tab ;