sql - SQL查询透视多对多表

Question

表格如下：

角色

    Id     Name
     1      Author

目的

    Id     Name
     1      Blog
     2      Post
     3      User

允许

    Id     Name
     1      Create
     2      Read
     3      Update
     4      Delete

角色对象权限

    RoleId     ObjectId     PermissionId
      1           1              2
      1           1              3
      1           2              1
      1           2              2
      1           2              3
      1           2              4

Role.Id = 1 的所需查询结果：

    Object    Create    Read    Update    Delete
     Blog       0         1        1         0
     Post       1         1        1         1
     User       0         0        0         0

我试图关注这个站点以及这个SO question，但这些示例仅适用于以列为中心的动态行和以行为中心的静态列。

查询一次只能用于一个角色。我需要查询来处理新对象和新权限

到目前为止我的 SQL：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';

SELECT 
    @columns += N', pi.'+QUOTENAME([PermissionName])
FROM
    (SELECT [Name] AS [PermissionName]
     FROM [dbo].[Permission] AS p
     GROUP BY [Name]) AS x;

SET @sql = N'

SELECT [ObjectName], ' + STUFF(@columns, 1, 2, '') + ' 
FROM 
    (SELECT 
         oP.[Name] AS ObjectName,
         (SELECT COUNT(rop.RoleID) 
          FROM [dbo].[RoleObjectsPermissions] rop, [dbo].[Object] o,
               [dbo].[Role] r, [dbo].[Permission] p 
          WHERE
              rop.RoleID = r.ID AND rop.ObjectID = o.ID 
              AND rop.PermissionID = p.ID 
              AND r.ID = rP.ID AND o.ID = oP.ID AND p.ID = pP.ID) AS [Quantity], 
         oP.[Name] 
     FROM
         [dbo].[RoleObjectsPermissions] ropP,
         [dbo].[Object] oP,
         [dbo].[Role] rP,
         [dbo].[Permission] pP 
     WHERE
         ropP.RoleID = rP.ID AND ropP.ObjectID = oP.ID 
         AND ropP.PermissionID = pP.ID 
         AND rP.ID = 2) AS j 
PIVOT (SUM(Quantity) FOR [Name] in 
               ('+STUFF(REPLACE(@columns, ', pi.[', ',['), 1, 1, '')+')
    ) AS pi;';

EXEC sp_executesql @sql

编辑 1：

我修改了表条目以提供角色对一个对象没有任何权限以及对另一个对象没有部分权限的场景

编辑 2：

这是最后一个@sql 变量，它给了我我想要的东西。我基本上采用了 Xedni 的答案，而不是内部加入所有关系表，而是加入了围绕所有对象的子查询。很酷的东西！

    @SQL = 
    concat
    ('
        select 
            [Object],
            ', @Exp, '
        from
        (
            select 
                [Object] = o.Name,
                sub.PermissionName,
                sub.PermissionId,
                sub.RoleName
            from dbo.Object o
            left join (
                select
                    ObjectId = rop.objectId,
                    PermissionName = p.name,
                    PermissionId = p.id,
                    RoleName = r.name
                from
                    dbo.RoleObjectsPermissions rop
                    inner join dbo.Permission p 
                        on rop.PermissionId = p.Id
                    inner join dbo.Role r
                        on rop.RoleId = r.Id
                where r.Id = 1
            ) sub on sub.ObjectId = o.ID
        ) s
        pivot (max(PermissionId) for PermissionName in (', @Fields, ')) p'
    )

标签： sqlsql-serverdatabasepivottranspose

Answer 1

/*****************************
Test Harness
*****************************/
if object_id('tempdb.dbo.#Role') is not null drop table #Role
create table #Role
(
    Id int primary key,
    Name nvarchar(128)
)

if object_id('tempdb.dbo.#Object') is not null drop table #Object
create table #Object
(
    Id int primary key,
    Name nvarchar(128)
)

if object_id('tempdb.dbo.#Permission') is not null drop table #Permission
create table #Permission
(
    Id int primary key,
    Name nvarchar(128)
)

if object_id('tempdb.dbo.#RoleObjectPermission') is not null drop table #RoleObjectPermission
create table #RoleObjectPermission
(
    RoleId int,
    ObjectId int,
    PermissionId int

)

insert into #Role values (1, 'Admin')
insert into #object values (1, 'Blog')
insert into #Permission
values
    (1, 'Create'),
    (2, 'Read'),
    (3, 'Update'),
    (4, 'Delete')
insert into #RoleObjectPermission
values
    (1, 1, 1),
    --(1, 1, 2),
    (1, 1, 3),
    (1, 1, 4)

/*********************
 Dynamic Pivot
*********************/
declare 
    @Fields nvarchar(max),
    @Exp nvarchar(max),
    @SQL nvarchar(max)

select 
    -- list of columns
    @Fields = 
        stuff
        (
            (
                select concat(',', quotename(name))
                from #Permission
                order by Id
                for xml path('')
            ), 1, 1, ''
        ),
    -- List of statements which evaluate to 0 if null
    @Exp = 
        stuff
        (
            (
                select concat(', ', quotename(name), ' = iif(', quotename(name), ' is null, 0, 1)')
                from #Permission
                order by Id
                for xml path('')
            ), 1, 1, ''
        ),
    @SQL = 
        concat
        ('
            select 
                [Object],
                ', @Exp, '
            from
            (
                select 
                    [Object] = o.Name,
                    PermissionName = p.name,
                    PermissionId = p.id,
                    RoleName = r.Name
                from #RoleObjectPermission rop
                inner join #Object o
                    on rop.ObjectId = o.Id
                inner join #Permission p 
                    on rop.PermissionId = p.Id
                inner join #Role r
                    on rop.RoleId = r.Id
                where r.Id = 1
            ) s
            pivot (max(PermissionId) for PermissionName in (', @Fields, ')) p'
        )

exec sp_executesql @SQL