查询管理员级别访问权限

Question

表格：

Admin
Id | User | Pass

Modules
Id | Name (ex. users, admin, gallery )

Permissions
Id | Name (ex. add, delete, read, update)

Levels
Id | Module (ex. gallery) | Cod_Permission (ex. 10) | Administrator (ex. user123)

所以我有查询：

SELECT
 n.Modulo,
 p.Nome AS Tipo,
 n.Administrador AS Nome,
 n.Cod_Permissao
 FROM
 niveis AS n
 Inner Join modulos AS m ON n.Modulo = m.Nome
 Inner Join permissoes AS p ON n.Cod_Permissao = p.Codigo
 WHERE
 n.Administrador =  'user123'

返回：

Id -> Module -> Cod_Permission -> Administator
 1    gallery    1    user123
 2    gallery    2    user123

我想将同一模块的所有结果分组在一行中，如下所示

Id -> Module -> Read -> Update -> Delete -> Insert -> Username
 1   gallery    1    0    0    4    user123

如何做到这一点？

Answer 1

像这样的东西 -

SELECT
    n.Modulo,
    p.Nome AS Tipo,
    n.Administrador AS Nome,
    SUM(IF(n.Cod_Permissao = 1, 1, 0)) AS Read,
    SUM(IF(n.Cod_Permissao = 2, 1, 0)) AS Update,
    SUM(IF(n.Cod_Permissao = 3, 1, 0)) AS Delete,
    SUM(IF(n.Cod_Permissao = 4, 1, 0)) AS Insert
FROM niveis AS n
INNER JOIN modulos AS m
    ON n.Modulo = m.Nome
INNER JOIN permissoes AS p
    ON n.Cod_Permissao = p.Codigo
WHERE n.Administrador = 'user123'
GROUP BY
    n.Modulo,
    p.Nome,
    n.Administrador

在不确切知道不同领域是什么的情况下，我不能 100% 确定，但这应该会让你朝着正确的方向前进。

Answer 2

尼科尔斯，好家伙！

解决方案：
您的查询相同，仅更改：

GROUP BY
    n.Modulo,
    p.Nome,
    n.Administrador

for

GROUP BY
    n.Modulo,
    n.Administrador

SELECT
    n.Modulo,
    p.Nome AS Tipo,
    n.Administrador AS Nome,
    SUM(IF(n.Cod_Permissao = 1, 1, 0)) AS Ler,
    SUM(IF(n.Cod_Permissao = 2, 2, 0)) AS Atualizar,
    SUM(IF(n.Cod_Permissao = 3, 3, 0)) AS Excluir,
    SUM(IF(n.Cod_Permissao = 4, 4, 0)) AS Inserir
FROM niveis AS n
INNER JOIN modulos AS m
    ON n.Modulo = m.Nome
INNER JOIN permissoes AS p
    ON n.Cod_Permissao = p.Codigo
WHERE n.Administrador = 'kin'
GROUP BY
    n.Modulo,
    n.Administrador