本示例仅适用于SQL Server 2005及以上版本
1.语法结构
语法结构基本上与PostgreSql的一致，不同之处在于with后面直接跟临时表表名，且内部使用"union all"连接。
with 临时表名称 as (
A.初始条件语句(非递归部分)
union all
B.递归部分语句
) [SELECT | INSERT | UPDATE | DELETE]
1.1 说明
- 前半部分A为初始条件语句，后半部分B为要进行的递归语句
- 先执行A语句，然后将A语句的结果作为B语句的条件，使用union all进行连接
2.示例
2.1 表结构
创建表信息
-- ----------------------------
-- Table structure for rbac_menu
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[rbac_menu]') AND type IN ('U'))
DROP TABLE [rbac_menu]
GO
CREATE TABLE [rbac_menu] (
[id] bigint NOT NULL,
[pid] bigint NULL,
[menu_name] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL
)
GO
ALTER TABLE [rbac_menu] SET (LOCK_ESCALATION = TABLE)
GO
EXEC sp_addextendedproperty
'MS_Description', N'ID',
'SCHEMA', N'dbo',
'TABLE', N'rbac_menu',
'COLUMN', N'id'
GO
EXEC sp_addextendedproperty
'MS_Description', N'父ID',
'SCHEMA', N'dbo',
'TABLE', N'rbac_menu',
'COLUMN', N'pid'
GO
EXEC sp_addextendedproperty
'MS_Description', N'菜单名称',
'SCHEMA', N'dbo',
'TABLE', N'rbac_menu',
'COLUMN', N'menu_name'
GO
-- ----------------------------
-- Records of rbac_menu
-- ----------------------------
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'100101', N'1001', N'权限管理')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'10010101', N'100101', N'菜单管理')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'10010102', N'100101', N'用户管理')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'10010103', N'100101', N'角色管理')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'1001010101', N'10010101', N'设置角色')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'1001010102', N'10010101', N'设置用户')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'1001010301', N'10010103', N'查看')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'1001010302', N'10010103', N'新增')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'1001010303', N'10010103', N'修改')
GO
INSERT INTO [rbac_menu] ([id], [pid], [menu_name]) VALUES (N'1001010304', N'10010103', N'删除')
GO
-- ----------------------------
-- Primary Key structure for table rbac_menu
-- ----------------------------
ALTER TABLE [rbac_menu] ADD CONSTRAINT [rbac_menu_pkey] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
查询表数据
select * from rbac_menu;
查询结果
2.2 获取所有子节点信息
with temp_table as (
-- 初始语句,仅执行一次
select "id", pid, "menu_name" from rbac_menu where pid = 1001
-- 使用union连接结果集(去重,不去重请使用"union all")
union all
-- 递归语句
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table b where a.pid = b."id"
)
select * from temp_table order by pid, "id";
查询结果
2.3 获取所有子节点信息(控制递归层数)
with temp_table as (
-- 初始语句,仅执行一次,设置一个变量 number=1
select
1 number, "id", pid, "menu_name"
from
rbac_menu
where
pid = 1001
-- 使用union连接结果集(去重,不去重请使用"union all")
union all
-- 递归语句,执行number次,控制number,即可控制递归的层次
select
(number+1) as n, a."id", a.pid, a."menu_name"
from
rbac_menu a,temp_table b
where
a.pid = b."id"
and number < 2 -- 递归2层
) select * from temp_table
order by pid, "id";
查询结果
2.4 获取所有父节点信息
with temp_table as (
select "id", pid, "menu_name" from rbac_menu where "id" = 1001010302
union all
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table b where a.id = b.pid
)
select * from temp_table order by pid desc, "id" desc;
查询结果
2.5 任意节点获取所有父节点及子节点信息(包括自身)
with temp_table as (
select "id", pid, "menu_name" from rbac_menu where "id" = 10010103
union all
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table b where a."id" = b.pid
), temp_table_b as (
select "id", pid, "menu_name" from rbac_menu where pid = 10010103
union all
select a."id", a.pid, a."menu_name" from rbac_menu a,temp_table_b b where a.pid = b."id"
)
select * from temp_table
union
select * from temp_table_b order by pid, "id";
查询结果
