sql去除多条重复数据（多字段）

最新推荐文章于 2025-10-26 16:10:16 发布

原创最新推荐文章于 2025-10-26 16:10:16 发布 · 6k 阅读

1 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

8 篇文章

订阅专栏

博客围绕重复数据处理展开，介绍了找到重复数据中的一条、所有重复数据的id、需删除的重复数据id的方法，还提及根据删除某条重复数据的sql演变，但指出该方法效率低，仅适用于小数据范围和手工删除。

1.找到重复数据中的一条数据：

SELECT id FROM s_course GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(sksj)>1

2.找到所有重复数据的id

SELECT * FROM s_course a WHERE EXISTS (
SELECT xn,xq,kcdm,kcmc,kkxy,jszgh,jsxm,xkkh,xkdd,sksj FROM (
SELECT xn,xq,kcdm,kcmc,kkxy,jszgh,jsxm,xkkh,xkdd,sksj FROM s_course GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(sksj)> 1) b 
WHERE a.xn=b.xn AND a.xq=b.xq AND a.kcdm=b.kcdm AND a.kcmc=b.kcmc AND a.kkxy=b.kkxy  AND a.jszgh=b.jszgh AND a.xkkh=b.xkkh AND a.xkdd=b.xkdd AND a.sksj=b.sksj)

3.找到所有需要删除的重复数据的id（即在2中除1外的id）

SELECT id FROM (
SELECT * FROM s_course a WHERE EXISTS (
SELECT xn,xq,kcdm,kcmc,kkxy,jszgh,jsxm,xkkh,xkdd,sksj FROM (
SELECT xn,xq,kcdm,kcmc,kkxy,jszgh,jsxm,xkkh,xkdd,sksj FROM s_course GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(sksj)> 1) b 
WHERE a.xn=b.xn AND a.xq=b.xq AND a.kcdm=b.kcdm AND a.kcmc=b.kcmc AND a.kkxy=b.kkxy  AND a.jszgh=b.jszgh AND a.xkkh=b.xkkh AND a.xkdd=b.xkdd AND a.sksj=b.sksj)
) c WHERE NOT EXISTS (SELECT id FROM (SELECT id FROM s_course  GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(jszgh)>1) d WHERE d.id=c.id)

4.根据删除重复的某一条重复数据sql演变

delete e from s_course e inner join (
SELECT id FROM s_course  GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(jszgh)>1) f on e.id=f.id

delete e from s_course e inner join (
SELECT id FROM (SELECT * FROM s_course a WHERE EXISTS (
SELECT xn,xq,kcdm,kcmc,kkxy,jszgh,jsxm,xkkh,xkdd,sksj FROM (
SELECT xn,xq,kcdm,kcmc,kkxy,jszgh,jsxm,xkkh,xkdd,sksj FROM s_course GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(sksj)> 1) b 
WHERE a.xn=b.xn AND a.xq=b.xq AND a.kcdm=b.kcdm AND a.kcmc=b.kcmc AND a.kkxy=b.kkxy  AND a.jszgh=b.jszgh AND a.xkkh=b.xkkh AND a.xkdd=b.xkdd AND a.sksj=b.sksj)) c WHERE NOT EXISTS (SELECT id FROM (SELECT id FROM s_course  GROUP BY xn,xq,kcdm,kcmc,kkxy,bjmc,jszgh,jsxm,xkkh,xkdd,sksj HAVING COUNT(jszgh)>1) d WHERE d.id=c.id)) f on e.id=f.id

效率低下，仅适用于小数据范围和手工删除数据