本文介绍了mySql N到N的双重联接以null-答案的交叉联接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个表,一个称为权限,一个称为角色,它们通过第三个表以N-N关系关联,称为RolePermissions.

I have two tables one called permissions and one called roles which are related through a third table called RolePermissions in a N-N relationship.

Permissions
 id <- PK
 name


Roles
 id <- PK
 name

RolePermisions
 RoleId  <-PK
 PermissionId <-PK
 isAllowed

我想要的是获取特定角色的权限的完整列表,并且在RolePermission表中没有该角色的值时为NULL.左联接通常可以解决问题,但我无法解决这一问题.

What I want is to get the full list of permissions of a specific role, and NULLs when there isn't a value in the RolePermission table for that Role. Left joins usually do the trick, but I can't work this one out.

基本上说我有以下值:

持久化:

1 - Per1
2 - Per2
3 - Per3

以及角色:

1 - Role1
2 - Role2

在RolePermissions中:

And in RolePermissions:

RoleId  -  PermissionId - isAllowed
 1            1             true
 1            2             false
 1            3             true
 2            1             true

以下查询排序有效,但对于那些不在RolePermissions中的值,它将不返回NULL:

The following query sorta works but it will not return NULLs for those values not in RolePermissions:

select permissions.name, rolepermissions.allowed 
FROM permissions LEFT JOIN rolepermissions  
    ON rolepermissions.permissionId = permissions.id 
WHERE rolepermissions.roleId = 2;

查询角色2时我要寻找的结果是

The result I would be looking for when querying for Role 2 is

Per1 - true
Per2 - NULL
Per3 - NULL

推荐答案

您可以使用 CROSS JOIN :

You can do this with a CROSS JOIN:

SELECT
    r.ID AS RoleID,
    p.ID AS PermissionID,
    rp.IsAllowed
FROM
    Roles r CROSS JOIN
    Permissions p LEFT JOIN
    RolePermissions rp ON rp.RoleId = r.ID AND rp.PermissionID = p.ID
WHERE r.ID = @RoleID

这篇关于mySql N到N的双重联接以null-答案的交叉联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-24 10:57