基于Project与Supplier组合处理MySQL重复记录并批量更新Status

原表数据 #

需求说明 #

• 规则1：同一Project下所有记录对应同一个Supplier时，保留其中一条（如最小Track_Id的记录）Status为P，其余设为F
• 规则2：同一Project下存在多个Supplier时，该Project下所有记录的Status统一设为F
• 需适配十万级数据量，保证查询效率

期望输出 #

解决方案（MySQL 8.0+ 推荐，高效适配大数据量） #

利用窗口函数和分组统计，一次性完成更新，避免多次扫描表：

WITH project_supplier_stats AS (
    -- 统计每个Project下的Supplier数量
    SELECT 
        Project,
        COUNT(DISTINCT supplier) AS supplier_count
    FROM Project_Supplier_Status
    GROUP BY Project
),
project_record_ranking AS (
    -- 对每个Project下的记录按Track_Id排序，标记第一条记录
    SELECT 
        Track_Id,
        Project,
        ROW_NUMBER() OVER (PARTITION BY Project ORDER BY Track_Id) AS row_num
    FROM Project_Supplier_Status
)
UPDATE Project_Supplier_Status pss
JOIN project_supplier_stats pss_stats ON pss.Project = pss_stats.Project
JOIN project_record_ranking prr ON pss.Track_Id = prr.Track_Id
SET pss.Status = CASE
    -- 当Project下有多个Supplier时，全部设为F
    WHEN pss_stats.supplier_count > 1 THEN 'F'
    -- 当只有一个Supplier时，第一条保留P，其余设为F
    ELSE CASE WHEN prr.row_num = 1 THEN 'P' ELSE 'F' END
END;

性能优化建议（针对十万级数据） #

1. 添加复合索引，大幅提升分组统计和排序效率：

CREATE INDEX idx_project_supplier_track ON Project_Supplier_Status(Project, supplier, Track_Id);

2. 若使用MySQL 5.x版本（不支持CTE），改用子查询关联实现：

UPDATE Project_Supplier_Status pss
JOIN (
    SELECT Project, COUNT(DISTINCT supplier) AS supplier_count
    FROM Project_Supplier_Status
    GROUP BY Project
) pss_stats ON pss.Project = pss_stats.Project
JOIN (
    SELECT 
        Track_Id,
        Project,
        @row_num := CASE WHEN @prev_project = Project THEN @row_num + 1 ELSE 1 END AS row_num,
        @prev_project := Project
    FROM Project_Supplier_Status, (SELECT @row_num := 0, @prev_project := '') AS init
    ORDER BY Project, Track_Id
) prr ON pss.Track_Id = prr.Track_Id
SET pss.Status = CASE
    WHEN pss_stats.supplier_count > 1 THEN 'F'
    ELSE CASE WHEN prr.row_num = 1 THEN 'P' ELSE 'F' END
END;

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