如何仅查询table2中外键对应[set]列值全相同的行？

现有两张表：

• table1：仅包含id列
• table2：包含id、op、set列

需求：筛选出在table2中所有对应行的set列值完全相同的id（例如id=a1的所有table2行set值一致，符合要求；id=a2的table2行set值有cut和slice两种，不符合要求）。

原查询语句无法得到正确结果：

SELECT DISTINCT(A.ID)
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.ID = B.ID
GROUP BY A.ID, B.SET
HAVING COUNT(DISTINCT(B.SET)) =1

原查询错误原因 #

原语句同时按A.ID和B.SET分组，这会把每个id下的不同set值拆分成独立分组，每个分组里的distinct set数量必然是1，导致所有关联的id都会被返回，完全不符合需求。

正确解法 #

解法一：分组统计distinct set数量

只按id分组，统计该id对应的set值的去重数量，仅保留数量为1的id：

SELECT A.id
FROM table1 A
JOIN table2 B ON A.id = B.id
GROUP BY A.id
HAVING COUNT(DISTINCT B.set) = 1;

解法二：排除存在多种set值的id

通过子查询找出那些存在至少两种不同set值的id，然后排除这些id：

SELECT DISTINCT A.id
FROM table1 A
JOIN table2 B ON A.id = B.id
WHERE A.id NOT IN (
    SELECT id
    FROM table2
    GROUP BY id
    HAVING COUNT(DISTINCT set) > 1
);

如果table1中的id在table2中没有对应行，若不需要保留这类id，上述两种解法都适用；若需要保留（即这类id默认符合“所有行set值相同”的逻辑），可以将JOIN改为LEFT JOIN，并调整条件。

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