如何编写SQL查询精准匹配同表另一列的指定值集合

表结构 #

Dummy表的数据如下：

需求说明 #

需要编写SQL查询，当指定一组Colum2的值时，返回对应的Colum1记录，要求该Colum1关联的所有Colum2值恰好完全匹配指定集合（值和数量都要一致）。例如指定值为(217,218)时，仅返回Colum1为219的记录。

当前问题 #

现有查询语句：

SELECT *
from Dummy
GROUP BY Colum1 
having count(distinct Colum2 ) = 2 and Colum2 in (217, 218);

返回结果不符合预期：
| 219 | 217 |
| 228 | 218 |
| 229 | 218 |

期望的正确输出应为：
| 219 | 217 |

解决方案 #

固定数量目标值的查询 #

原查询的问题在于：分组后直接引用Colum2属于非聚合列，逻辑不严谨；同时没有排除那些存在额外Colum2值的记录（比如228关联了225、229关联了220）。

正确的查询逻辑是先筛选出满足两个条件的Colum1：

1. 关联的Colum2数量等于指定集合的元素个数
2. 所有Colum2值都在指定集合内，无额外值

对应的SQL如下：

SELECT d1.Colum1, d1.Colum2
FROM Dummy d1
INNER JOIN (
    SELECT Colum1
    FROM Dummy
    GROUP BY Colum1
    HAVING COUNT(DISTINCT Colum2) = 2  -- 替换为指定集合的元素数量
       AND SUM(CASE WHEN Colum2 NOT IN (217, 218) THEN 1 ELSE 0 END) = 0
) d2 ON d1.Colum1 = d2.Colum1;

动态数量目标值的适配 #

如果指定的Colum2值数量不固定（2-5个），可以用临时表存储目标值，避免手动修改数字：

1. 创建临时表并插入目标值：

CREATE TEMPORARY TABLE TargetValues (val INT);
INSERT INTO TargetValues VALUES (217), (218);  -- 动态插入需要的目标值

2. 执行查询：

SELECT d1.Colum1, d1.Colum2
FROM Dummy d1
INNER JOIN (
    SELECT d.Colum1
    FROM Dummy d
    LEFT JOIN TargetValues tv ON d.Colum2 = tv.val
    GROUP BY d.Colum1
    -- 确保Colum2数量等于目标值数量，且所有Colum2都匹配目标值
    HAVING COUNT(DISTINCT d.Colum2) = (SELECT COUNT(*) FROM TargetValues)
       AND COUNT(tv.val) = COUNT(d.Colum2)
) d2 ON d1.Colum1 = d2.Colum1;

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