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 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 涂聚文图书位置目录第一层楼第二排第三层 */ |
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 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 , ',' )) |
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步