-
SQL递归函数列出父级的所有子级(ID ParentID模式)
-
-
-
-
-
- Create function [dbo].[GetChild](@ID varchar(10))
- returns @t table(ID varchar(10),ParentID varchar(10),Level int)
- as
- begin
- declare @i int
- set @i = 1
- insert into @t select @ID,@ID,0
- insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
-
- while @@rowcount<>0
- begin
- set @i = @i + 1
- insert into @t
- select
- a.ID,a.ParentID,@i
- from
- Dept a,@t b
- where
- a.ParentID=b.ID and b.Level = @i-1
- end
- return
- end
-
-
-
- Declare @Id Int
- Set @Id = 24;
-
- With RootNodeCTE(Id,ParentId)
- As
- (
- Select Id,ParentId From Dept Where ParentId In (@Id)
- Union All
- Select Dept.Id,Dept.ParentId From RootNodeCTE
- Inner Join Dept
- On RootNodeCTE.Id = Dept.ParentId
- )
-
- Select * From RootNodeCTE
-
-
-
-
-
-
- create table Dept(ID int,ParentID int,msg varchar(20))
- insert into Dept select 1,0,null
- insert into Dept select 2,1,null
- insert into Dept select 3,1,null
- insert into Dept select 4,2,null
- insert into Dept select 5,3,null
- insert into Dept select 6,5,null
- insert into Dept select 7,6,null
- go
-
-
-
- Create function [dbo].[GetChild](@ID varchar(10))
- returns @t table(ID varchar(10),ParentID varchar(10),Level int)
- as
- begin
- declare @i int
- set @i = 1
- insert into @t select @ID,@ID,0
- insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
-
- while @@rowcount<>0
- begin
- set @i = @i + 1
- insert into @t
- select
- a.ID,a.ParentID,@i
- from
- Dept a,@t b
- where
- a.ParentID=b.ID and b.Level = @i-1
- end
- return
- end
-
-
-
- select ID from dbo.GetChild(3)
- go
-
-
- /*
- 3
- 5
- 6
- 7
- */
-
-
- drop function GetChild
- drop table Dept
-
-
-
相关阅读:
【C++】获取环境变量
浅谈ADO.NET和EntityFramework以及Nhibernate
Entity Framework(EF)
线程安全以及锁
JWT认证
ADO.NET
jmeter请求https接口,需要设置证书
git remote: HTTP Basic: Access denied
2021~2022年度全国小语“十大青年名师”评选活动, 播放地址、下载地址
20192420 任文朗 202120222 《网络与系统攻防技术》实验八实验报告
-
原文地址:https://www.cnblogs.com/zhangchenliang/p/2391383.html
Copyright © 2020-2023
润新知