sql: T-SQL parent-child function script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
--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
   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    涂聚文图书位置目录第一层楼第二排第三层
*/

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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 ;

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
--- 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
   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,','))

  

 

posted @   ®Geovin Du Dream Park™  阅读(269)  评论(0编辑  收藏  举报
努力加载评论中...
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8
点击右上角即可分享
微信分享提示