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

 

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