选择具有一组特定特征的行

Question

很抱歉，我实在想不出一个更好的描述，同时保持文字简短。这是完整的问题。

在有两列的表中，我希望在第一列中选择一个条目，该条目具有我在第二列中指定的所有值。用一个简单的例子(也在SQL Fiddle上)可以更好地解释这一点。

假设我有一个这样的表:

CREATE TABLE t (fruit TEXT, color TEXT);
INSERT INTO t (fruit, color) VALUES
('banana', 'green'),
('banana', 'yellow'),
('apple', 'green'),
('apple', 'red'),
('apple', 'yellow'),
('strawberry', 'green'),
('strawberry', 'red');

给定一些颜色，我需要检索具有所有这些颜色的水果(您可以假设只有一个)-不多也不少。

如果我查询"green and red"我应该买"草莓"。但是，如果我查询"绿色和黄色"我应该看到"香蕉"而不是"苹果";因为后者也有一个额外的颜色

到目前为止，我想到的是一个可怕的黑客使用INTERSECT:

WITH cte AS (
  SELECT fruit FROM t WHERE color = 'green'
  INTERSECT SELECT fruit FROM t WHERE color = 'yellow')
SELECT fruit FROM cte;

当然失败了，因为返回香蕉和苹果。

谁有更好的计划?

Answer 1

您可以使用布尔聚合:

select fruit
from the_table
group by fruit
having bool_and(color in ('green', 'yellow'));

如果你想用INTERSECT来做，你可以使用这样的东西:

-- all fruits that have at least those two colors 
select fruit
from t
where color in ('green', 'yellow')
group by fruit
having count (distinct color) = 2
intersect 
-- all fruits that have exactly two colors 
select fruit
from t
group by fruit
having count(distinct color) = 2

Answer 2

demo:db<>fiddle

您可以聚合颜色并检查聚合的数组是否等于您期望的颜色。

SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color ORDER BY color) = '{green, yellow}'

,

演示:db&lt的在小提琴

要得到阶无关的解，你可以这样做:

SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color) @> '{yellow, green}'
   AND ARRAY_AGG(color) <@ '{yellow, green}'

或

SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color ORDER BY color) = 
   (SELECT ARRAY_AGG(unnest ORDER BY unnest) FROM unnest('{yellow, green}'::text[]))