SQL 如何删除多列值重复的行并保留maxPercent最高的记录
问题根因
你写的这条查询逻辑有明显偏差:当前语句只判断了“8个min列值相同且id不同”,本质是把所有存在重复的行全查出来了,完全没叠加「同重复组下只留maxPercent最高值」的判断,肯定得不到预期结果。
对应解决方法
方案1:窗口函数实现(兼容MySQL8.0+/PostgreSQL/SQL Server等主流新版本数据库)
核心逻辑是把8个min列的值组合作为分组依据,给每个重复组内的行按maxPercent降序排名,排名为1的就是要保留的行,其余行全部可以剔除。
- 查询需要保留的行:
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY minA, minB, minC, minD, minE, minF, minG, minH ORDER BY maxPercent DESC, id ASC ) AS row_rank FROM duplicates ) ranked_result WHERE row_rank = 1;
补充:
ORDER BY后面加id ASC是为了避免同组出现maxPercent值完全相等的情况时,出现随机选行的问题,用id兜底可以保证结果稳定。
- 如果需要直接删除表中多余的重复行,对应执行以下删除语句即可:
DELETE FROM duplicates WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY minA, minB, minC, minD, minE, minF, minG, minH ORDER BY maxPercent DESC, id ASC ) AS row_rank FROM duplicates ) ranked_result WHERE row_rank > 1 );
方案2:兼容老版本数据库(如MySQL5.x这类不支持窗口函数的环境)
直接在你原有写的exists语句基础上,补充“同组存在maxPercent值比当前行更大的行”的判断,就可以精准筛出需要删除的行:
DELETE t1 FROM duplicates t1 WHERE EXISTS ( SELECT 1 FROM duplicates t2 WHERE -- 匹配8个min列完全一致的同组重复行 t2.minA = t1.minA AND t2.minB = t1.minB AND t2.minC = t1.minC AND t2.minD = t1.minD AND t2.minE = t1.minE AND t2.minF = t1.minF AND t2.minG = t1.minG AND t2.minH = t1.minH -- 核心补充:同组存在比当前行maxPercent更大的行,当前行就是需要删除的 AND t2.maxPercent > t1.maxPercent );
注意事项
实际跑之前要留意,如果这8个min列里存在NULL值,普通的=等值判断是失效的——SQL中判断两个NULL值相等的结果为未知,根本不会被识别成同组重复。这时候需要换成NULL安全的比较方式:
- MySQL环境下用
<=>运算符替换原来的等值判断 - PostgreSQL环境下用
IS NOT DISTINCT FROM做等价判断
按你描述的样例数据,执行上述语句后,最终表中会仅保留id为1和id为5的行,完全符合你提出的筛选规则。
内容的提问来源于stack exchange,提问作者Maria1995
相关产品推荐
相关产品推荐

