SQL Server递归查询

本示例仅适用于SQL Server 2005及以上版本

1.语法结构

语法结构基本上与PostgreSql的一致，不同之处在于with后面直接跟临时表表名，且内部使用"union all"连接。

with 临时表名称 as (
    A.初始条件语句(非递归部分)
    
    union all
    
    B.递归部分语句
) [SELECT | INSERT | UPDATE | DELETE]

1.1 说明

1. 前半部分A为初始条件语句，后半部分B为要进行的递归语句
2. 先执行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";

查询结果