有数据表
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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。