MySQL/SQLite SELECT Any 和 NONE

Question

假设我有这两个表:

表用户:

ID           Name
1            User A
2            User B
3            User C
4            User D
5            User E

表权限:

ID           UserID          PermissionName
1            1               read
2            3               read
3            3               write
4            5               read
5            5               write
6            5               admin

现在我想有以下查询:

1- 所有有写权限的用户

SELECT u.* FROM user u, permission p WHERE p.userID = u.id AND p.name = 'write' GROUP BY u.id;

好像还可以。返回用户 3 和 5。

2-所有没有写权限的用户

SELECT u.* FROM user u, permission p WHERE p.userID = u.id AND p.name != 'write' GROUP BY u.id;

返回:1,3,5预期:1,2,4

这是行不通的。它返回每个用户，除了那些只有写权限的用户。那么我怎样才能使它在 MySQL 和 SQLite 中工作呢？

3- 两者的结合

• 所有具有读写权限的用户 (3,5)
• 所有只读不写的用户 (1)
• 等等

Answer 1

GROUP BY u.id 简单地组合了满足WHERE 条件的每个用户的所有行。由于用户 1、3 和 5 具有 write 以外的权限，因此它们将被返回。

解决这个问题最简单的方法就是更改权限表。像这样:

表权限:

ID        UserID        CanRead        CanWrite      IsAdmin
1         1             true           false         false
2         3             true           true          false
3         5             true           true          true

现在很明显它可以成为用户表的一部分:

表用户:

ID     Name        CanRead        CanWrite      IsAdmin
1      User A      true           false         false
2      User B      false          false         false
3      User C      true           true          false
4      User D      false          false         false
5      User E      true           true          true

因此您甚至不需要权限表。查询现在变为:

SELECT * FROM user WHERE CanWrite = 'false'

这很简单....但你认为我在作弊吗？

您可能因过度规范化的冲动而受苦。