分组匹配objectId获取runId时结果不符合预期的SQL问题

现有数据库中已存在Candidate表，通过CTE定义了Proposed数据集，需求是匹配Candidate与Proposed的objectId、name字段，筛选出对应runId的记录与Proposed完全一致（无多余、无缺失记录）的runId。当前执行的查询返回了runId 1和2，但预期仅返回runId 1，需修正查询逻辑。

表结构与测试数据 #

CREATE TABLE Candidate (
  runId  bigint,
  objectId bigint,
  name VARCHAR(100)
);

INSERT INTO Candidate(runId, objectId, name) VALUES (1, 401, 'object1');
INSERT INTO Candidate(runId, objectId, name) VALUES (1, 402, 'object2');
INSERT INTO Candidate(runId, objectId, name) VALUES (1, 403, 'object3');
INSERT INTO Candidate(runId, objectId, name) VALUES (1, 404, 'object4');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 401, 'object1');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 402, 'object2');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 403, 'object3');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 404, 'object4');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 405, 'object5');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 406, 'object6');
INSERT INTO Candidate(runId, objectId, name) VALUES (2, 406, 'object7');

Proposed数据集（CTE） #

原查询语句 #

SELECT c.runid
FROM candidate c
INNER JOIN proposed p ON c.objectid = p.objectid AND c.name = p.name
GROUP BY c.runid
HAVING COUNT(DISTINCT c.objectid) = (SELECT COUNT(*) FROM proposed);

原输出结果 #

预期输出结果 #

修正方案 #

原查询的问题在于仅验证了匹配到的记录数与Proposed总数一致，但没有排除那些包含额外不匹配记录的runId（比如runId=2）。要实现完全匹配，需要同时满足两个核心条件：

1. 该runId匹配到的记录数等于Proposed的总数（确保无缺失）
2. 该runId在Candidate中没有任何不匹配Proposed的记录（确保无多余）

修正后的查询方式一（通过统计总记录数匹配） #

SELECT c.runid
FROM candidate c
LEFT JOIN proposed p ON c.objectid = p.objectid AND c.name = p.name
GROUP BY c.runid
HAVING 
  -- 总记录数等于Proposed的数量，确保无多余
  COUNT(DISTINCT CONCAT(c.objectid, c.name)) = (SELECT COUNT(*) FROM proposed)
  -- 匹配成功的记录数等于Proposed的数量，确保无缺失
  AND COUNT(p.objectid) = (SELECT COUNT(*) FROM proposed);

修正后的查询方式二（通过NOT EXISTS排除不匹配记录） #

SELECT c.runid
FROM candidate c
INNER JOIN proposed p ON c.objectid = p.objectid AND c.name = p.name
GROUP BY c.runid
HAVING 
  -- 匹配成功的记录数等于Proposed的数量，确保无缺失
  COUNT(DISTINCT CONCAT(c.objectid, c.name)) = (SELECT COUNT(*) FROM proposed)
  -- 检查该runId下没有不匹配Proposed的记录，确保无多余
  AND NOT EXISTS (
    SELECT 1 
    FROM candidate c2 
    WHERE c2.runid = c.runid
    AND NOT EXISTS (
      SELECT 1 
      FROM proposed p2 
      WHERE p2.objectid = c2.objectid AND p2.name = c2.name
    )
  );

逻辑说明 #

• COUNT(DISTINCT CONCAT(c.objectid, c.name)) 用于统计每个runId下唯一的(objectId, name)组合数，避免重复记录干扰判断
• 方式一通过左连接后统计匹配成功的数量和总数量是否一致，直接排除有额外记录的runId
• 方式二通过嵌套NOT EXISTS，直接检查当前runId下是否存在任何不匹配Proposed的记录，确保完全一致

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