MySQL / SQLite选择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

现在很明显，它可能成为users表的一部分：

表用户：

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'

太简单了..。但你认为我在作弊吗？

你可能会遭受过度正常化的冲动。

Answer 2

我找到了一个可行的解决方案，它可以与许多其他过滤器相结合。也许不是最有表现力的方式，但似乎没问题。

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

SELECT * FROM user WHERE id NOT IN (SELECT userID FROM permission WHERE permission = 'write') ;

3-所有具有读写的用户

SELECT * FROM user WHERE id NOT IN (SELECT userID FROM permission WHERE permission = 'write') AND id IN (SELECT userID FROM permission WHERE permission = 'read') ;