SqlServer父节点与子节点查询及递归

在最近老是用到这个SQL，所以记下来了：

1：创建表

CREATE TABLE [dbo].[BD_Booklet](
[ObjID] [int] IDENTITY(1,1) NOT NULL,
[ParentID] [int] NULL,
[ObjLen] [int] NULL,
[ObjName] [nvarchar](50) NULL,
[ObjUrl] [nvarchar](200) NULL,
[ObjExpress] [nvarchar](500) NULL,
[ObjTime] [nvarchar](50) NULL,
[ObjUID] [nvarchar](10) NULL,
[ObjDemo] [text] NULL,
CONSTRAINT [PK_BD_Booklet] PRIMARY KEY CLUSTERED
(
[ObjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

2：添加数据（自己添加）

3：根据父节点查询子节点信息（所有子节点，包括子节点的子节点）

--公用表表达式实现父节点查询子节点
DECLARE @ParentID int
SET @ParentID='1'
with CTEGetChild as
(
select * from BD_Booklet where ParentID=@ParentID
UNION ALL
(SELECT a.* from BD_Booklet as a inner join
CTEGetChild as b on a.ParentID=b.ObjID
)
)
SELECT * FROM CTEGetChild

4：根据节点得到最初始的父节点（根节点）（）

--公用表表达式实现子节点查询父节点
DECLARE @ChildID int
SET @ChildID=6
DECLARE @CETParentID int
select @CETParentID=ParentID FROM BD_Booklet where ObjID=@ChildID
with CTEGetParent as
(
select * from BD_Booklet where ObjID=@CETParentID
UNION ALL
(SELECT a.* from BD_Booklet as a inner join
CTEGetParent as b on a.ObjID=b.ParentID
)
) SELECT * FROM CTEGetParent

 说明|：近期发现该方法只能查询出三级，对于4级和5级的没有办法。

下面的SQL可以查询多级

 

5：N级节点查询

DECLARE @ParentID int
SET @ParentID='4'
with CTEGetChild as(
select *
from BD_Booklet 
where (PID in(@ParentID) or objid in (@ParentID) ) and JGID =2
UNION ALL
select
b.*
from CTEGetChild
inner join BD_Booklet b on CTEGetChild.ObjID=b.PID
)
select distinct * from CTEGetChild where Killed= 0