如何修改SQL分组查询，筛选同时含指定值及关联表匹配值的分组

需求说明 #

需要从table1中筛选出满足以下两个条件的PID：

• 该PID下至少有一个PRODUCT存在于table2的FRUIT列表中
• 该PID下同时包含potato产品
  期望结果为PID 1和PID 3

原SQL的问题 #

原SQL按PID和PRODUCT分组，只能单独筛选出每个符合水果表的产品记录，无法对整个PID进行聚合判断，也无法验证该PID是否包含potato。

解决方案1：分组聚合验证 #

通过先过滤有效产品，再按PID分组，用聚合函数检查是否包含目标产品：

SELECT PID, COUNT(*) AS 有效产品数量
FROM table1
WHERE PRODUCT IN (SELECT FRUIT FROM table2)
GROUP BY PID
HAVING SUM(CASE WHEN PRODUCT = 'potato' THEN 1 ELSE 0 END) > 0;

逻辑解释

1. WHERE子句：先筛选出table1中PRODUCT存在于table2的记录，减少后续分组的数据处理量
2. GROUP BY PID：将同一用户的所有有效产品归为一组
3. HAVING子句：用SUM(CASE...)统计当前PID分组中potato的出现次数，大于0则说明该用户包含目标产品

解决方案2：子查询验证存在性 #

用EXISTS子查询直接验证同一PID下是否存在potato：

SELECT DISTINCT t1.PID
FROM table1 t1
WHERE t1.PRODUCT IN (SELECT FRUIT FROM table2)
AND EXISTS (
    SELECT 1 FROM table1 t2
    WHERE t2.PID = t1.PID AND t2.PRODUCT = 'potato'
);

逻辑解释

1. 外层查询先筛选出table1中符合水果表的产品记录
2. EXISTS子查询检查当前PID对应的记录中是否存在potato
3. DISTINCT去重，确保同一个PID只返回一次

两种方案最终都会返回期望的PID 1和PID 3。

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