Netezza SQL：如何筛选同时含指定值的ID的所有行？

原始数据表 #

id  color    food
1  1    red cookies
2  1   blue   pizza
3  1  green    cake
4  2    red   pasta
5  2    red    cake
6  2    red cookies
7  3   blue   pizza
8  3  green   pizza
9  3 yellow   pizza

需求说明 #

筛选出所有行中同时存在color='red'和food='pizza'的ID（两个条件无需在同一行），并返回该ID的全部行。示例中仅ID=1符合要求，预期结果如下：

# Expected Results
  id  color    food
1  1    red cookies
2  1   blue   pizza
3  1  green    cake

错误尝试分析 #

你之前使用的SQL：

SELECT * 
FROM my_table 
WHERE id IN (
    SELECT id 
    FROM my_table 
    WHERE (color = 'red' OR food = 'pizza') 
);

返回全表数据的原因是：OR条件会把只要包含red，或者只要包含pizza的ID全部纳入，比如ID2有red、ID3有pizza，所以所有ID都被选中了，不符合“同时存在两个条件”的要求。

正确实现方法 #

方法1：双IN子查询取交集 #

通过两个独立的子查询分别找出有red的ID和有pizza的ID，再取两者的交集：

SELECT * 
FROM my_table 
WHERE id IN (SELECT id FROM my_table WHERE color = 'red')
  AND id IN (SELECT id FROM my_table WHERE food = 'pizza');

方法2：GROUP BY + HAVING 条件聚合 #

对ID分组后，用条件聚合判断每个ID是否同时满足两个条件，再关联原表获取全量行：

SELECT t.*
FROM my_table t
JOIN (
    SELECT id
    FROM my_table
    GROUP BY id
    HAVING MAX(CASE WHEN color = 'red' THEN 1 ELSE 0 END) = 1
       AND MAX(CASE WHEN food = 'pizza' THEN 1 ELSE 0 END) = 1
) filtered_ids ON t.id = filtered_ids.id;

方法3：双EXISTS验证 #

通过两次EXISTS分别验证当前ID是否存在red行和pizza行：

SELECT *
FROM my_table t
WHERE EXISTS (SELECT 1 FROM my_table WHERE id = t.id AND color = 'red')
  AND EXISTS (SELECT 1 FROM my_table WHERE id = t.id AND food = 'pizza');

测试数据（R语言） #

my_table = data.frame(
    id = c(1,1,1,2,2,2,3,3,3),
    color = c("red", "blue", "green", "red", "red", "red", "blue", "green", "yellow"),
    food = c("cookies", "pizza", "cake", "pasta", "cake", "cookies", "pizza", "pizza", "pizza")
)

内容的提问来源于stack exchange，提问作者stats_noob