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

 

--- Parent Child Parent-Child Relationship 
-- 职位层次，用来数据权限的范围
DROP TABLE Employees 
GO
create table Employees
(
	empid int IDENTITY(1,1)  PRIMARY KEY,
	empname nvarchar(100),
	managerID int 
)
go

insert into Employees(empname,managerID) values(N'黄经济',0)
insert into Employees(empname,managerID) values(N'黄未央',1)
insert into Employees(empname,managerID) values(N'王历朝',1)
insert into Employees(empname,managerID) values(N'涂聚文',3)
insert into Employees(empname,managerID) values(N'Geovin Du',4)
insert into Employees(empname,managerID) values(N'geovindu',2)
insert into Employees(empname,managerID) values(N'geovin',6)
insert into Employees(empname,managerID) values(N'du',4)

select * from Employees
GO




DROP TABLE WorkDay 
GO
create table WorkDay
(
	Worid int IDENTITY(1,1)  PRIMARY KEY,
	WorkName nvarchar(100),
	WorkManagerID int
		FOREIGN KEY REFERENCES Employees(empid),
	WorkDate datetime default(getdate())
)
go


insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'开全部门会议审批',4,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'办理银行卡业',5,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'采购电脑',6,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'参供应商会议',4,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'办理社保业务',5,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'采购办公用品',6,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'办理物业业务',5,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'采购礼仪用品',6,getdate())
insert into WorkDay(WorkName,WorkManagerID,WorkDate) values(N'考察供应商信誉',6,getdate())
go

select * from WorkDay
GO



---empid,empname,managerID


--查位置所有子结点,带路径与排序 
 if object_id('GetEmployeesParentLevel') is not null 
drop function GetEmployeesParentLevel 
 go 
 create function GetEmployeesParentLevel
(
	@id int
)  
 returns @re table(empid int,managerID int,empname nvarchar(100),[level] int,sort varchar(100),empnameFullName nvarchar(500))  
 as  
 begin 
     declare @l int  
     set @l=0  
     insert @re 
  select empid,managerID,empname,@l,right('000'+ltrim(empid),3),empname  from Employees where managerID=@id 
     while @@rowcount>0 
     begin  
         set @l=@l+1 
         insert @re 
   select a.empid,a.managerID,a.empname,@l,b.sort+right('000'+ltrim(a.empid),3), b.empnameFullName+''+a.empname from Employees as a,@re as b  
   where b.empid=a.managerID and b.[level]=@l-1 
     end 
     update @re set [level] = [level] 
     return  
 end  
 go  

select * from GetEmployeesParentLevel(0)


---得书目录的子節點函數表（包含本身）
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[[GetEmployeesChildrenId]]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetEmployeesChildrenId]
GO
Create Function [GetEmployeesChildrenId]
(
	@ID int
)
Returns @Tree Table (empid Int,managerID Int, empname NVarchar(180))
As
Begin
Insert @Tree Select empid,managerID, empname From Employees Where empid = @ID
While @@Rowcount > 0
Insert @Tree Select A.empid, A.managerID, A.empname From Employees A Inner Join @Tree B On A.managerID = B.empid And A.empid Not In (Select empid From @Tree)--- 
Return
End
GO


select * from dbo.[GetEmployeesChildrenId](4)

---得到位置子节点列表ID地址函數（包含本身）涂聚文(Geovin Du）
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetEmployeesGroupId]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetEmployeesGroupId]
GO
Create Function [dbo].[GetEmployeesGroupId]
(
	@empid int
)
RETURNS NVARCHAR(max)
AS
BEGIN
declare @allstring nvarchar(max),@top nvarchar(200)--,@BookPlaceID int
--set @BookPlaceID=2
set @allstring='' 
select @allstring=@allstring+cast(empid as varchar(10))+',' FROM [dbo].[GetEmployeesChildrenId](@empid) ORDER BY empid  --where CompanyID<>@CompanyID
set  @allstring=LEFT(@allstring,LEN(@allstring)-1)
--select @allstring
RETURN @allstring
END
GO

select  [dbo].[GetEmployeesGroupId] (4)

-- 涂聚文(Geovin Du） 测试
declare @linkmanno int ,@name nvarchar(500),@str nvarchar(50),@key varchar(50)
set @name=''
select  @key=[dbo].[GetEmployeesGroupId] (4)
SELECT * FROM Employees  where empid in (SELECT value FROM string_split(@key,','))
select * from WorkDay where WorkManagerID in (SELECT value FROM string_split(@key,','))