SQL 查询查找多个条件的匹配项

Question

如果我有一个如下所示的 PERMISSIONS 表：

PERSON         PERMISSION
------         ----------
Bob            red
John           red
John           blue
Mary           red
Mary           blue
Mary           yellow

和一个如下所示的 THINGS 表：

THING          PERMISSION
-----          ----------
apple          red
eggplant       red
eggplant       blue

我正在尝试提出一个纯 SQL 查询，它可以让我找出 PERSON可以访问THING。基本上，我想要一个看起来像这样的查询：

SELECT person
  FROM ... vague handwaving here ...
 WHERE thing = 'eggplant'

并让它返回“John”和“Mary”。关键是访问该事物所需的权限数量是任意的。

我觉得这应该是显而易见的，但我只是想不出一个优雅的解决方案。首选 Oracle 兼容解决方案。

编辑：

Kosta 和 JBrooks 的解决方案效果很好。下面是 Kosta 解决方案的修改版本，仅命中索引两次，而 Kosta 的解决方案为 3 倍，JBrooks 的为 4 倍（尽管我同意 JBrooks 的观点，即这可能是不必要的优化）。

SELECT p.person, num_permission, COUNT(p.person)
FROM permissions p
INNER JOIN (
    SELECT permission,
           COUNT(1) OVER (PARTITION BY thing) AS num_permission
      FROM things
     WHERE thing = 'eggplant'
  ) t ON t.permission = p.permission 
GROUP BY p.person, num_permission
HAVING COUNT(p.person) = num_permission

Answer 1

select person
from permissions 
where permission in (select permission from things where thing='eggplant')
group by person
having count(person) = (select count(permission)  from things where thing='eggplant')

Answer 2

用途：

  SELECT p.person
    FROM PERMISSIONS p
    JOIN THINGS t ON t.permission = p.permission
   WHERE t.permission IN ('red', 'blue')
GROUP BY p.person
  HAVING COUNT(DISTINCT t.permission) = 2

WHERE 子句确保仅包含红色和蓝色值。 COUNT DISTINCT 确保不允许重复（两个蓝色），因为这将是误报。

Answer 3

好吧，我明白为什么人们可能会使用 Count() 来匹配，这可能会起作用，但我认为当事情变得更复杂时，你会遇到麻烦（而且它们总是变得更复杂。）

如果我用英语说这个问题是：

1. Select the PEOPLE that has
   对该事物的许可。
2. 并且不存在权限
   该事物所需的
   人没有。

所以 SQL 应该是：

SELECT DISTINCT P.PERSON, T.THING
FROM PERMISSIONS P
INNER JOIN THINGS T
ON P.PERMISSION = T.PERMISSION
WHERE NOT EXISTS
    (SELECT 1
    FROM THINGS TSUB
    WHERE TSUB.THING = T.THING
    AND TSUB.PERMISSION NOT IN
        (SELECT PSUB.PERMISSION
        FROM PERMISSIONS PSUB
        WHERE PSUB.PERSON = P.PERSON))
ORDER BY P.PERSON, T.THING

我现在是 SQL Server 人员，所以语法可能有点不对劲，但你明白了。

性能：有人会说这看起来不像是最高效的 SQL，所以现在让我为自己辩护。与系统的其余部分相比，权限表和用户表通常较小，并且使用当今的 DBMS，您将很难在这些表中加载足够的数据以使该语句的运行时间超过十分之一秒 - 特别是在索引被删除之后在使用中。所以在过去我会同意表演——现在我从不担心表演，除非它突然出现在我面前。当您以这种方式处理时，维护工作就会少得多。

Answer 4

select distinct person
from permissions p
join things      t on t.permission = p.permission
                  and t.thing      = 'eggplant'

应该这样做。