如何在SQL中查询所有列完全匹配的重复数据对应的记录id？

你原有SQL的GROUP BY逻辑本身是按candidateid、bgvtype、DepartmentId、BUId、CustomerId、ProjectId六个列完全相同来分组的，逻辑没问题，但存在两个问题导致结果不符合预期：

• HAVING条件冗余：同一分组内的上述六个列值本身完全一致，不需要单独对每个列做COUNT判断，只要COUNT(*) > 1就代表该组存在重复
• 仅返回candidateid：你拿到candidateid后查询全表时，会命中该candidateid下其他列值不同、不属于重复分组的记录，才会出现ProjectId不同的记录被误算的错觉

方案1：窗口函数实现（适配支持窗口函数的数据库：SQL Server、MySQL 8+、PostgreSQL等） #

如果需要直接获取所有重复记录的主键ID，推荐用窗口函数实现：

仅返回重复组中除第一条外的重复记录 #

WITH duplicate_groups AS (
    SELECT 
        id, -- 替换为你表中的实际主键ID字段名
        ROW_NUMBER() OVER (
            PARTITION BY candidateid, bgvtype, DepartmentId, BUId, CustomerId, ProjectId 
            ORDER BY id
        ) AS rn
    FROM table1 WITH (NOLOCK)
    WHERE status IN (0,1)
)
SELECT id FROM duplicate_groups WHERE rn >= 2;

返回重复组中的所有记录（包含组内第一条） #

WITH duplicate_groups AS (
    SELECT 
        id,
        COUNT(*) OVER (
            PARTITION BY candidateid, bgvtype, DepartmentId, BUId, CustomerId, ProjectId
        ) AS group_cnt
    FROM table1 WITH (NOLOCK)
    WHERE status IN (0,1)
)
SELECT id FROM duplicate_groups WHERE group_cnt > 1;

方案2：子查询关联实现（适配不支持窗口函数的数据库，如MySQL 5.x） #

SELECT t1.id
FROM table1 t1 WITH (NOLOCK)
INNER JOIN (
    SELECT 
        candidateid, bgvtype, DepartmentId, BUId, CustomerId, ProjectId
    FROM table1 WITH (NOLOCK)
    WHERE status IN (0,1)
    GROUP BY candidateid, bgvtype, DepartmentId, BUId, CustomerId, ProjectId
    HAVING COUNT(*) > 1
) t2 
ON  t1.candidateid = t2.candidateid
AND t1.bgvtype = t2.bgvtype
AND t1.DepartmentId = t2.DepartmentId
AND t1.BUId = t2.BUId
AND t1.CustomerId = t2.CustomerId
AND t1.ProjectId = t2.ProjectId
WHERE t1.status IN (0,1);

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