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

GO

Create Function GetBookPlaceChildrenId

(

	@ID int

)

Returns @Tree Table (BookPlaceID Int,BookPlaceParent Int, BookPlaceName NVarchar(180))

As

Begin

Insert @Tree Select BookPlaceID,BookPlaceParent, BookPlaceName From BookPlaceList Where BookPlaceID = @ID

While @@Rowcount > 0

Insert @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)---

Return

End

GO

select * 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]

GO

Create Function [dbo].[GetBookPlaceGroupId]

(

	@BookPlaceID int

)

RETURNS NVARCHAR(200)

AS

BEGIN

declare @allstring nvarchar(200),@top nvarchar(200)--,@BookPlaceID int

--set @BookPlaceID=2

set @allstring=''

select @allstring=@allstring+cast(BookPlaceID as varchar(10))+',' FROM [dbo].[GetBookPlaceChildrenId](@BookPlaceID) ORDER BY BookPlaceID  --where CompanyID<>@CompanyID

set  @allstring=LEFT(@allstring,LEN(@allstring)-1)

--select @allstring

RETURN @allstring

END

GO

select  [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<>1

select * from GetBookPlaceParentLevel (0) where BookPlaceID<>1

select * from GetBookPlaceParentLevel (0) where BookPlaceID<>1 and [level]=1 

--測試結果

/*

2	1	第一层楼	1	001002	涂聚文图书位置目录第一层楼

3	1	第二层楼	1	001003	涂聚文图书位置目录第二层楼

4	2	第一排	2	001002004	涂聚文图书位置目录第一层楼第一排

6	2	第二排	2	001002006	涂聚文图书位置目录第一层楼第二排

7	4	第二层	3	001002004007	涂聚文图书位置目录第一层楼第一排第二层

8	4	第三层	3	001002004008	涂聚文图书位置目录第一层楼第一排第三层

5	4	第一层	3	001002004005	涂聚文图书位置目录第一层楼第一排第一层

12	4	第四层	3	001002004012	涂聚文图书位置目录第一层楼第一排第四层

9	6	第一层	3	001002006009	涂聚文图书位置目录第一层楼第二排第一层

10	6	第二层	3	001002006010	涂聚文图书位置目录第一层楼第二排第二层

11	6	第三层	3	001002006011	涂聚文图书位置目录第一层楼第二排第三层

*/

declare @id int

set @id = 3

;with t as--如果CTE前面有语句，需要用分号隔断

(

select BookKindID, BookKindParent, BookKindName

from BookKindList

where BookKindID = @id

union all

select r1.BookKindID,r1.BookKindParent,r1.BookKindName

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