创建一个 SQL 视图，显示来自 3 个表（如交叉表）的数据

Question

我正在使用 ASP.net 会员系统和 SQL Server 2000 数据库。我想要做的是创建一个视图（或存储过程，如果更好的话），它将返回一个包含以下列的表：

UserName  |  Role1  |  Role2  |  Role3  |  ....  | Role*N*

其中生成列（除了用户名）以包含应用程序中的所有 ASP.net 角色。

我需要这是通用的（即，当我添加新角色时，我希望过程/视图进行补偿并将该新角色列为新列）。如果用户属于该角色，我希望“角色”字段中的值为 1（真）或 0（假）。我可以使用动态 SQL 创建一个包含此类列的表来创建可变数量的列，但我似乎无法填充它们。

我怎样才能做到这一点？

涉及的表如下所示：

CREATE TABLE [dbo].[aspnet_Users](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [LoweredUserName] [nvarchar](256) NOT NULL,
    [MobileAlias] [nvarchar](16) NULL,
    [IsAnonymous] [bit] NOT NULL,
    [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
) ON [PRIMARY]
) ON [PRIMARY]



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,
PRIMARY KEY NONCLUSTERED 
(
    [RoleId] ASC
) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[aspnet_UsersInRoles](
    [UserId] [uniqueidentifier] NOT NULL,
    [RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [UserId] ASC,
    [RoleId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

视图无法根据其中的数据动态添加列。直接查询也不能。由于您希望它根据表中的数据自动更改，因此这完全排除了使用 pivot 等结构的视图和查询。

考虑到您给我们的限制，您几乎唯一的选择是生成一个具有足够列（动态）的临时表，并填充它。这仍然是有问题的，因为存储过程无法创建并返回临时表，因为存储过程退出时该表将被删除。

另一种选择是编写一个存储过程，动态生成并执行数据透视查询。但这会非常混乱。新角色的添加是否如此频繁以至于这是绝对的要求？这是唯一让它变得如此困难的事情。

为您提供所需输出的静态数据透视查询可能如下所示：

select
  UserName,
  [1] as Role1, [2] as Role2, [3] as Role3 -- UpdateMe
from (
  select
    u.UserName,
    r.RoleID
    IsUserInRole(u.UserName, r.RoleName) RoleFlag
  from
    aspnet_users u
    cross join aspnet_roles r) source
  pivot (
    RoleFlag for RoleID in 
      ([1],[2],[3]) -- UpdateMe
  ) PivotTable

您的动态 SQL 生成器必须保持更新的两行，它们用 UpdateMe 注释进行标记。

我这里没有安装 MSSQL，所以我无法测试这个查询，但它应该非常接近。 pivot 参考可能会有所帮助。

使用聚合和 case 在 SQL Server 2000 中伪造数据透视表：

select
  UserName,
  max(case when src.RoleID = 1 then RoleFlag else 0 end) Role1,  --Repeat N times
  ...
from (
  select
    u.UserName,
    r.RoleID
    IsUserInRole(u.UserName, r.RoleName) RoleFlag
  from
    aspnet_users u
    cross join aspnet_roles r) src
group by
  UserName