递归公用表表达式(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 | 研发部->副总经理室->总经理室 |
更多使用场景
本文作者:KeepChasing
本文链接:https://www.cnblogs.com/idbb98/p/17046956.html
版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步