mysql - 包括 MySQL 中的其余行

Question

我有一个 SQL 查询，它选择用户的权限，并向其添加 true。

SELECT
    PrivilageName,
    'true' hasrights  <-- imaginary column
FROM
    users
NATURAL JOIN usermemberships
NATURAL JOIN groupprivileges
NATURAL JOIN `privileges`
WHERE
    UserID = '2'

结果是

AddBuilding           true
RemoveBuilding        true
EditBuilding          true

我正在尝试使用 false 值添加其余权限。

AddBuilding           true
RemoveBuilding        true
EditBuilding          true
RemoveUser            false
AddUser               false

我该怎么做？

编辑:表的结构:

用户(UserID),
用户成员(member)资格(用户ID，组ID)，
groupprivileges(GroupID, PrivilegeID),
权限(PrivilegeID，PrivilageName)

编辑:拼写错误，抱歉。

Answer 1

(注意:此答案中的查询现已更新，以包含添加到问题中的列名称。)

获取该结果集的一种方法是使用 LEFT JOIN 操作(在 ON 计算中使用适当的谓词)，代替所有这些 NATURAL JOIN 操作。

(我只是猜测 NATURAL JOIN 引用的列名称。为了破译它，我们需要检查每个表定义以获取所有列的列表，然后找到所有匹配的列名，以找出 MySQL 使用哪些列来执行这些内连接操作。)

根据查询文本中提供的少量信息，这是我将采取的方法(同样，只是猜测每个 ON 子句中引用的名称):

SELECT p.PrivilageName
     , IF(u.UserID IS NOT NULL,'true','false') AS hasrights
  FROM `privileges` p
  LEFT
  JOIN groupprivileges g 
    ON g.PrivilegeID = p.PrivilegeID
  LEFT
  JOIN usermemberships m
    ON m.GroupId = g.GroupID
  LEFT
  JOIN users u
    ON u.UserID = g.UserID
   AND u.UserID = 2

取决于这些表中的基数(即“AddBuilding”权限是否授予两个不同的组，一个用户是其成员，另一个不是......)

并取决于您是否希望避免返回任何“重复”PrivilageName 值(多行包含“true”或“false”，或者同时包含“true”和“false”的行)每个 PrivilageName)，并取决于您希望结果集如何排序(即您是否希望首先列出所有“真实”权限？)...

那么这个查询在返回的结果集中更具确定性，它只会返回每个 PrivilageName 一次。这个结果集似乎更适合回答用户是否拥有特权的问题。

SELECT p.PrivilageName
     , MAX(IF(u.UserID IS NOT NULL,'true','false')) AS hasrights
  FROM `privileges` p
  LEFT
  JOIN groupprivileges g 
    ON g.PrivilegeID = p.PrivilegeID
  LEFT
  JOIN usermemberships m
    ON m.GroupId = g.GroupID
  LEFT
  JOIN users u
    ON u.UserID = g.UserID
   AND u.UserID = 2
 GROUP BY p.PrivilageName
 ORDER BY hasrights DESC, p.PrivilageName ASC

(就我个人而言，我会省略 ORDER BY，并让结果按 PrivilageName 排序，但使用 ORDER BY，这可以更好地匹配问题中指定的结果集。)

<小时/>

当然，这不是获取结果集的唯一方法，但它可能是最有效的(给定合适的索引)。

就我个人而言，我从不使用NATURAL JOIN。 (我想查看语句中的谓词，并且如果有人将具有匹配名称的列添加到我的查询中的一个表中，我不希望我的任何查询“中断”。(实际上，考虑一下，我无法使用 NATURAL JOIN，因为 id 通常是几乎所有表的主键列的名称...外键列通常命名为 referencedtable_id。)但是即使我确实以可以使用 NATURAL JOIN 的方式命名列，我也发现潜在的缺点超过了任何优点。

但是，类似下面的语句可能会起作用。 (我说“可能”是因为我没有任何使用这样的语法的经验......我从不使用自然连接，而且我总是更喜欢左连接而不是右连接。如果我店里有人带着这个来找我，我会给他们上面的语句。但我不想给您留下这样的印象:NATURAL JOIN 不能用于返回指定的结果集。您指定的结果集可能会由像这样的声明:

SELECT
    PrivilageName,
    MAX(IF(UserID=2,'false','true')) AS hasrights
FROM
    users
NATURAL RIGHT JOIN usermemberships
NATURAL RIGHT JOIN groupprivileges
NATURAL RIGHT JOIN `privileges`
GROUP BY PrivilageName