MySQL运维——查询重复数据、删除重复记录

1、先确认重复数据

-- 查询重复的project+dept组合及重复数量
SELECT 
    project,
    dept,
    COUNT(*) AS duplicate_count  -- 给计数结果加别名，便于理解
FROM projectdept 
GROUP BY project, dept 
HAVING COUNT(*) > 1;

2、删除重复记录的语句

-- 方式1：使用JOIN+子查询（推荐，兼容性好）
DELETE t1 
FROM projectdept AS t1
INNER JOIN (
    -- 先获取每个project+dept组合的最小ID
    SELECT project, dept, MIN(id) AS min_id
    FROM projectdept
    GROUP BY project, dept
    HAVING COUNT(*) > 1  -- 只处理有重复的组合
) AS t2 
ON t1.project = t2.project 
AND t1.dept = t2.dept 
AND t1.id > t2.min_id;  -- 删除ID大于最小ID的重复记录

-- 方式2：如果是MySQL 8.0+，可用窗口函数（更简洁）
DELETE FROM projectdept
WHERE id IN (
    SELECT id 
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY project, dept ORDER BY id) AS rn
        FROM projectdept
    ) AS t
    WHERE rn > 1  -- 保留rn=1（ID最小），删除rn>1的重复项
);