sql - 合并所有子查询中的结果

Question

我有一个 postgre 表，其表示与下面的数据类似。

迎合表:

-----------------------
| Name    | Option     |
-----------------------
| jane    | social      |
| jane    | vegan       |
| jane    | gmo-free    |
| jane    | italian     |
| jack    | social      |
| jack    | corporate   |
| jack    | gmo-free    |
| jack    | greek       |
| rodz    | social      |
| rodz    | wedding     |
| rodz    | gmo-free    |
| rodz    | vegan       |
| rodz    | french      |

这是我要运行的“伪”查询

SELECT * FROM caters 
WHERE option is either ['italian', 'french'] 
AND WHERE option is both ['wedding', 'social']

这个伪查询应该返回 rodz。因为它既有意大利语也有法语，而且它既有婚礼又有社交。

这是我为完成 sudo 查询而尝试编写的查询

SELECT c.name FROM caters c
WHERE c.option in ('italian', 'french')
GROUP BY c.name
HAVING array_agg(c.option) @> array['wedding', 'social']

这怎么没有返回任何结果。单独运行查询

SELECT c.name FROM caters c
WHERE c.option in ('italian', 'french')
GROUP BY c.name

结果:

-----------
| Name    |
-----------
| jane    | // has italian
| rodz    | // has french

其他查询

SELECT c.name FROM caters c
GROUP BY c.name
HAVING array_agg(c.option) @> array['wedding', 'social']

结果:

-----------
| Name    |
-----------
| rodz    | // has wedding and social

所以我可以单独看到查询是正确的。这让我觉得很好，如果我有 2 个查询给我正确的结果，只需要过滤掉两个查询中的结果，为什么我不 JOIN 它们。

所以我试过了

SELECT c.name FROM caters c
JOIN caters c1 
ON c1.name = c.name and c1.option = c.option
WHERE c1.option in ('italian', 'french')
GROUP BY c.name
HAVING array_agg(c.option) @> array['wedding', 'social']

但这也没有结果。知道我该怎么做吗？

注意:每次运行时查询都是动态的，所使用的值可能不同，有时可能是 5 种语言，有时是 2 种语言，如本例 ('italian', 'french')。举个例子，我所说的动态查询的意思是另一个查询可以是

SELECT * FROM caters 
WHERE option is either ['italian'] 
AND WHERE option is both ['corporate', 'social']
// returns none
----------------------------------------------------------
SELECT * FROM caters 
WHERE option is either ['french', 'greek'] 
AND WHERE option is either ['gmo-free', 'vegan'] 
AND WHERE option is both ['corporate', 'social']
// returns jack
----------------------------------------------------------
SELECT * FROM caters WHERE option is ['social']
// returns jack, and rodz

Answer 1

您可以尝试使用相关子查询

DEMO

select distinct name from tablename a 
where option in ('italian', 'french') and exists
(
  select 1 from tablename b where a.name=b.name and option in ('wedding', 'social')
  group by b.name having count(distinct option)=2
)

输出:

name
rodz