递归公用表表达式（CTE）的使用

递归查询示例

Common Table Expressions(CTE)

注意事项

在递归公用表表达式的递归部分不允许使用外部联接。

表数据

RowID	ID	Name	PID
1	10001	总经理室	NULL
2	10002	副总经理室	10001
3	20001	研发部	10002
4	20002	采购部	10002
5	20003	人事部	10002

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dept](
	[RowID] [int] IDENTITY(1,1) NOT NULL,
	[ID] [nvarchar](10) NULL,
	[Name] [nvarchar](20) NULL,
	[PID] [nvarchar](10) NULL,
 CONSTRAINT [PK_Dept] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Dept] ON 
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (1, N'10001', N'总经理室', NULL)
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (2, N'10002', N'副总经理室', N'10001')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (3, N'20001', N'研发部', N'10002')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (4, N'20002', N'采购部', N'10002')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (5, N'20003', N'人事部', N'10002')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (6, N'30001', N'研发组A', N'20001')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (7, N'30002', N'研发组B', N'20001')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (8, N'30010', N'原料采购组', N'20002')
GO
INSERT [dbo].[Dept] ([RowID], [ID], [Name], [PID]) VALUES (9, N'30011', N'办公采购组', N'20002')
GO
SET IDENTITY_INSERT [dbo].[Dept] OFF
GO

父节点查子节点

/**
查总经理室(10001)所有下级部门
**/
WITH DeptCTE AS (
	SELECT ID,Name,CAST('总经理室'+'->'+Name AS NVARCHAR(MAX)) AS DPATH FROM Dept WHERE PID='10001'
	UNION ALL
	SELECT b.ID,b.Name,a.DPATH+'->'+b.Name FROM DeptCTE a JOIN Dept b ON a.ID=b.PID
)
SELECT * FROM DeptCTE;

ID	Name	DPATH
10002	副总经理室	总经理室->副总经理室
20001	研发部	总经理室->副总经理室->研发部
20002	采购部	总经理室->副总经理室->采购部
20003	人事部	总经理室->副总经理室->人事部
30010	原料采购组	总经理室->副总经理室->采购部->原料采购组
30011	办公采购组	总经理室->副总经理室->采购部->办公采购组
30001	研发组A	总经理室->副总经理室->研发部->研发组A
30002	研发组B	总经理室->副总经理室->研发部->研发组B

子节点查父节点

/**
查研发部(20001)所有上级部门
**/
WITH DeptCTE2 AS (
	SELECT ID,Name,PID,CAST(Name AS NVARCHAR(MAX)) AS DPATH FROM Dept WHERE ID='20001'
	UNION ALL
	SELECT b.ID,b.Name,b.PID,a.DPATH+'->'+b.Name FROM DeptCTE2 a JOIN Dept b ON a.PID=b.ID
)
SELECT * FROM DeptCTE2 WHERE ID<>'20001';

ID	Name	PID	DPATH
10002	副总经理室	10001	研发部->副总经理室
10001	总经理室	NULL	研发部->副总经理室->总经理室

更多使用场景

1. 合并列值