sql语句递归查询

表结构： 

给出一个结点找到该节点的所有  子  节点： 

WITH C_Depts AS
(
 SELECT dept.* FROM Department dept WHERE dept.PPtr='父节点ID'
    UNION ALL
 SELECT dept.* FROM C_Depts ,Department dept WHERE dept.PPtr=C_Depts .DeptID
)
SELECT C_Depts .DeptID FROM C_Depts 

 给出一个结点找到该节点的所有  父  节点：

WITH P_Depts AS
(
 SELECT dept.* FROM Department dept WHERE dept.DeptID='结点ID' 
    UNION ALL
 SELECT dept.* FROM P_Depts ,Department dept WHERE dept.DeptID=P_Depts .PPtr
)
SELECT P_Depts .DeptID FROM P_Depts 

也可以在  WITH 后跟其他语句，比如我要实现的功能是将DeptID=“001001”及下级单位删除。（即将DeptID=“001001”及子节点全部删除）

WITH C_Depts AS
(
 SELECT dept.* FROM Department dept WHERE dept.PPtr='001001'
    UNION ALL
 SELECT dept.* FROM C_Depts ,Department dept WHERE dept.PPtr=C_Depts .DeptID
)
delete Department where  DeptID 
in (SELECT C_Depts .DeptID FROM C_Depts )

附建库和生成数据的语句：

/******建库******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Deptment](
	[DeptID] [varchar](50) NOT NULL,
	[DeptName] [nvarchar](200) NULL,
	[PPtr] [varchar](50) NULL,
 CONSTRAINT [PK_Deptment] PRIMARY KEY CLUSTERED 
(
	[DeptID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/*********插入数据***********/
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'001', N'部门列表', NULL)
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'001001', N'第一大部门', N'001')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'001002', N'第二大部门', N'001')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'2F09ED6B90074CE99339F5615A075529', NULL, N'a3e46530-cb26-4879-948e-09233ec4678b')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'37dc11eb-74a0-44e0-ab8b-6ae2e502e249', N'综合部', N'001001')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'5193021e-d358-43d8-bfb9-aa042f82dcfc', N'综合部1', N'37dc11eb-74a0-44e0-ab8b-6ae2e502e249')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'553274d8-2d86-46cd-8677-9e9190eda125', N'综合部2', N'001001')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'6cb65a7f-128b-4fe5-92f7-f11e2aff28b4', N'发展部', N'5193021e-d358-43d8-bfb9-aa042f82dcfc')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'7a39c46d-c405-4ad0-b703-ef42884f4071', NULL, N'001002')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'a3e46530-cb26-4879-948e-09233ec4678b', N'技术部', N'001002')
INSERT [dbo].[Deptment] ([DeptID], [DeptName], [PPtr]) VALUES (N'e6e46430-cb20-4809-949e-09233ec408b1', N'领导班子', N'37dc11eb-74a0-44e0-ab8b-6ae2e502e249')