SQL查询以查找多个条件的匹配项 [英] SQL query to find matches for multiple criteria

问题描述

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

If I had a PERMISSIONS table that looked like this:

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

和如下所示的THINGS表:

and a THINGS table that looks like this:

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

我正在尝试提出一个纯SQL查询，该查询将使我找出哪些PERSON可以访问哪些THING.基本上，我希望查询看起来像这样:

I'm trying to come up with a pure SQL query that would let me find out what PERSONs have access to what THINGs. Basically, I want a query that would look something like:

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

，并返回"John"和"Mary".关键是访问事物所需的许可数量是任意的.

and have it return "John" and "Mary". The key point being the number of permissions necessary for access to the thing is arbitrary.

我觉得这应该很明显，但是我无法提出一个优雅的解决方案.首选与Oracle兼容的解决方案.

I feel like this should be obvious, but I just can't come up with an elegant solution. Oracle compatible solutions preferred.

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

Solutions from Kosta and JBrooks work well. Below is a modified version of Kosta's solution that only hits the indexes twice, as opposed to 3x for Kosta's and 4x for JBrooks's (though I agree with JBrooks that this is probably unnecessary optimization).

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

推荐答案

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')

这篇关于SQL查询以查找多个条件的匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！