SQL - 填充组表

Question

给定以下表格/关系

编辑： 如果使用下面的 SQL 进行填充应该返回这个....

SELECT     TOP (100) PERCENT dbo.Task.Name AS Expr1, dbo.Role.Name FROM dbo.Role INNER JOIN dbo.RoleTask ON dbo.Role.Id = dbo.RoleTask.RoleId INNER JOIN                       dbo.Task ON dbo.RoleTask.TaskId = dbo.Task.Id ORDER BY dbo.Task.Name, dbo.Role.Name

Aim

我正在尝试填充 RoleGroup （那个是只是挂在那里！）以及由角色和任务之间的多对多关系定义的角色组，认识到其中一些可能已经在 RoleGroup 表中。

编辑：因此，考虑到上面的示例结果，这就是我需要在 RoleGroup 中看到的内容（自原始帖子以来我已经对此进行了修改，希望能够更清楚地说明我想要实现的目标) ...

GroupId      RoleId
1            Plumber
2            Gardener
2            Topiary Guru
3            Electrician
4            Cleaner
4            Housekeeping Supervisor
4            Toilet Cleaning Specialist  
5            Housekeeping Supervisor

结果说明

由于角色已与某些任务关联，因此可以识别角色组。

在我的示例中，“清洁工、客房服务主管和厕所清洁专家”都与“厕所清洁”任务相关联。因此，我可以说这是一个团体，并且想提取该信息。

同样，“客房主管”已与“厕所检查”任务相关联；而其他角色则没有。这意味着应提取另一个新组（2 - 客房服务主管）。

如果“客房服务主管”与其他任务相关联，并且没有其他角色，则我不需要创建另一个组，因为它已经被识别。

哦，我正在尝试使用 SQL Server 2008 中的 SQL 来实现此目的。

任何提示或提示都值得赞赏。

SQL

USE TestDatabase
GO

CREATE TABLE [dbo].[Task](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RoleGroup](
    [Id] [int] NOT NULL,
    [RoleId] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Role](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [DepartmentId] [int] NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RoleTask](
    [RoleId] [int] NOT NULL,
    [TaskId] [int] NOT NULL,
 CONSTRAINT [PK_RoleTask] PRIMARY KEY CLUSTERED 
(
    [RoleId] ASC,
    [TaskId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  ForeignKey [FK_Role_Department]    Script Date: 05/20/2011 17:56:49 ******/
ALTER TABLE [dbo].[Role]  WITH CHECK ADD  CONSTRAINT [FK_Role_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO
ALTER TABLE [dbo].[Role] CHECK CONSTRAINT [FK_Role_Department]
GO

/****** Object:  ForeignKey [FK_RoleTask_Role]    Script Date: 05/20/2011 17:56:49 ******/
ALTER TABLE [dbo].[RoleTask]  WITH CHECK ADD  CONSTRAINT [FK_RoleTask_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([Id])
GO
ALTER TABLE [dbo].[RoleTask] CHECK CONSTRAINT [FK_RoleTask_Role]
GO

/****** Object:  ForeignKey [FK_RoleTask_Task]    Script Date: 05/20/2011 17:56:49 ******/
ALTER TABLE [dbo].[RoleTask]  WITH CHECK ADD  CONSTRAINT [FK_RoleTask_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([Id])
GO
ALTER TABLE [dbo].[RoleTask] CHECK CONSTRAINT [FK_RoleTask_Task]
GO

/** DATA **/

INSERT INTO [Department] ([Id], [Name]) VALUES (1, 'Housekeeping');
INSERT INTO [Department] ([Id], [Name]) VALUES (2, 'Security');
INSERT INTO [Department] ([Id], [Name]) VALUES (3, 'External Maintenance');
INSERT INTO [Department] ([Id], [Name]) VALUES (4, 'Internal Maintenance');

INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (1, 'Cleaner', 1);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (2, 'Housekeeping Supervisor', 1);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (3, 'Toilet Cleaning Specialist', 1);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (4, 'Security Guard', 2);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (5, 'Electrician', 4);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (6, 'Plumber', 4);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (7, 'Gardener', 3);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (8, 'Topiary Guru', 3);

INSERT INTO [Task] ([Id], [Name]) VALUES (1, 'Toilet Clean');
INSERT INTO [Task] ([Id], [Name]) VALUES (2, 'Light Out');
INSERT INTO [Task] ([Id], [Name]) VALUES (3, 'Blocked Sink');
INSERT INTO [Task] ([Id], [Name]) VALUES (4, 'Toilet Inspection');
INSERT INTO [Task] ([Id], [Name]) VALUES (5, 'Leaky Tap');
INSERT INTO [Task] ([Id], [Name]) VALUES (6, 'Bush too bushy');
INSERT INTO [Task] ([Id], [Name]) VALUES (7, 'Mop Floor');

INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (1, 1);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (2, 1);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (3, 1);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (5, 2);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (6, 3);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (2, 4);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (6, 5);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (7, 6);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (8, 6);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (1, 7);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (2, 7);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (3, 7);

Answer 1

我不确定我是否完全理解了您的问题，但正如评论者所建议的，您的架构可能有问题。

一方面，角色组是隐藏在不同名称下的角色；根据您的架构的唯一区别是它不一定属于某个部门。但话又说回来，您也可以对角色说同样的话：即使在大型公司中，也经常有一个用户/角色向多个老板报告，因此属于多个部门。

所以你真正看到的是一个组层次结构。如果您将事情抽象化，您会很高兴能够一次性将一项任务分配给多个角色。您可以合理地将角色和角色组转储到单个表、角色以及允许将它们相互关联的角色到角色表中：某些角色分配其他角色，但反之则不然；有些角色属于多个角色组；并且角色组有多个组；它是一个有向图，其主键为 (ParentId, Id)，两者都引用 Roles(Id)。

此外，出于所有意图和目的，您的 RoleTask 表已经回答了您的查询。但您仍然需要一个名为 RoleGroup 的新角色。如果我得到您在问题评论中给出的解释，添加新任务和新的关联角色会自动导致新的角色组。这个单射属性应该暗示任务也是隐藏在不同名称下的角色。

此外，请注意，某些任务可以分解为多个单独的任务，并且这些单独的子任务可能是多个较大任务的一部分。这也是一个有向图。

无论如何，这就是说您应该考虑将所有这些表合并在一起。为了能够轻松添加外键，您可能希望将它们分成三部分，但如果您这样做，我建议将 Tasks 和 RoleGroup 修剪为 Id 字段，该字段既是主键又是外键角色（Id）的关键。

出于同样的原因，您可能还想查看 Department 表并在那里应用相同的逻辑。它可能需要一些额外的细节，例如 ManagerId，但最终它也是一个不同名称的角色：它的主要目的是将用户分组在一起，也许一个部门内的所有用户都应该获得在某些情况下执行某些任务。它正好适合上面描述的有向图。

最后但并非最不重要的一点是，可能值得指出的是，单个用户（未在您的图表中显示，但肯定存在于您的模式中）本身也是角色。一项任务很可能是专门分配给个人而不是角色或群体的。另外，你永远不知道经理什么时候想将他的权限委托给他的秘书，因为他月底休假。

现在，可以说，使用大量的表和它们之间的关系来管理整个混乱当然是可能的，就像您现在尝试做的那样。但是您也可以引入两个新表，例如 Perm（如在权限中）和 PermPerm（如在权限分配中）。并使各个节点（即User、Dept、Role、Task）继承自Perm。这样做将允许您从单个表管理整个权限图：PermPerm。

哦，如果您有一个小时的时间，请考虑观看此视频：ACL 已失效。它对权限管理有有趣的见解：