--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 ;