SQL Server 2005公用表达式实现递归

有数据表

1.

CREATE TABLE [dbo].[aspnet_Roles]
(
[ApplicationId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar] (256) NOT NULL,
[LoweredRoleName] [nvarchar] (256) NOT NULL,
[Description] [nvarchar] (256) NULL
) ON [PRIMARY]

2.

CREATE TABLE [dbo].[pms_RoleRelations]
(
[RoleId] [uniqueidentifier] NOT NULL,
[ParentRoleId] [uniqueidentifier] NOT NULL
) ON [PRIMARY]


GO
EXEC sp_addextendedproperty N'MS_Description', N'角色间的关系', 'SCHEMA', N'dbo', 'TABLE', N'pms_RoleRelations', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_Description', N'角色ID', 'SCHEMA', N'dbo', 'TABLE', N'pms_RoleRelations', 'COLUMN', N'RoleId'
GO
EXEC sp_addextendedproperty N'MS_Description', N'父角色ID', 'SCHEMA', N'dbo', 'TABLE', N'pms_RoleRelations', 'COLUMN', N'ParentRoleId'

有数据：

1. 

DE2051D2-E76F-4E68-BD80-2BD7A183BE35    70D40086-FCAA-4F58-A802-F6A808B9BDF0    myrole    myrole
DE2051D2-E76F-4E68-BD80-2BD7A183BE35    15F9D7AC-895C-4FE3-A21E-CFC3CCFBE117    myrole1    myrole1
DE2051D2-E76F-4E68-BD80-2BD7A183BE35    ADEA0D48-209E-4E78-8927-60C3B834F463    myrole12    myrole12
DE2051D2-E76F-4E68-BD80-2BD7A183BE35    D052D277-6413-4418-94EB-648CE067CCD1    myrole2    myrole2
DE2051D2-E76F-4E68-BD80-2BD7A183BE35    F876778D-C1E9-4F37-9BF9-530C09E6D129    myrole3    myrole3
DE2051D2-E76F-4E68-BD80-2BD7A183BE35    C9AE4104-A900-4228-96E4-B9E12D10EB77    Test    test

2.

F876778D-C1E9-4F37-9BF9-530C09E6D129    70D40086-FCAA-4F58-A802-F6A808B9BDF0
ADEA0D48-209E-4E78-8927-60C3B834F463    15F9D7AC-895C-4FE3-A21E-CFC3CCFBE117
D052D277-6413-4418-94EB-648CE067CCD1    70D40086-FCAA-4F58-A802-F6A808B9BDF0
C9AE4104-A900-4228-96E4-B9E12D10EB77    ADEA0D48-209E-4E78-8927-60C3B834F463
15F9D7AC-895C-4FE3-A21E-CFC3CCFBE117    70D40086-FCAA-4F58-A802-F6A808B9BDF0

要实现：

取得 Test 的所有祖先角色，即，得到myrole12,myrole1,myrole

 

实现方式：

CREATE PROCEDURE [dbo].[pms_Roles_GetAllAncestorRoles]
    @ApplicationName           nvarchar(256),
    @RoleId                        char(36)
AS
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
   
    IF (@ApplicationId IS NULL)
        RETURN;
  
   --使用公用表达式实现递归取得角色的所有祖先
    WITH RoleRelations(RoleId,ParentRoleId,RoleName,pName,pDescription,Level)
    AS
    (
        SELECT r.RoleId,ParentRoleId,r.RoleName AS rName, p.rolename AS pName,p.Description,0 AS Level
        FROM aspnet_Roles r
        LEFT JOIN pms_RoleRelations
            ON pms_RoleRelations.RoleId = r.RoleId
        LEFT JOIN aspnet_Roles p
            ON p.RoleId = pms_RoleRelations.ParentRoleId
        WHERE r.RoleId = @RoleId
            AND r.ApplicationId = @ApplicationId

        UNION ALL
       
        SELECT r.RoleId,pid.ParentRoleId,r.RoleName AS rName, pn.rolename AS pName,pn.Description,Level + 1
        FROM aspnet_Roles r
        JOIN pms_RoleRelations pid
            ON r.roleid = pid.roleid
        JOIN aspnet_Roles pn ON pn.roleid = pid.parentroleid
        JOIN RoleRelations p ON p.ParentRoleId = r.RoleId
        WHERE r.ApplicationId = @ApplicationId
    )
    SELECT ParentRoleId AS RoleId,
            pName AS RoleName ,
            pDescription AS Description
    FROM RoleRelations