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