dm7根据部分字段去重

洋哥哥db

已于 2023-03-13 09:37:02 修改

阅读量451

点赞数

分类专栏： dm 文章标签： sql 数据库 mysql

于 2020-04-26 17:08:01 首次发布

本文链接：https://blog.youkuaiyun.com/qq_33674508/article/details/105772835

版权

dm 专栏收录该内容

52 篇文章

订阅专栏

delete from test.cf where (FDMCHXM,TYSHXYDM) IN
(SELECT FDMCHXM,TYSHXYDM FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)>1)
AND ROWID not IN (SELECT MIN(ROWID) FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)>1);

更高效方式：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

说明：

重复数据总量=
重复数据量
select COUNT(*) from test.cf
where (FDMCHXM,TYSHXYDM)
in(select fdmchxm,TYSHXYDM FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)>1)
AND ROWID NOT IN (SELECT MIN(ROWID) FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)>1);
+
重复数据量去重后数据量
select COUNT(*) from test.cf
where (FDMCHXM,TYSHXYDM)
in(select fdmchxm,TYSHXYDM FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)>1)
AND ROWID IN (SELECT MIN(ROWID) FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)>1);

总数据量=
没有重复数据量
select COUNT(*) from test.cf
where (FDMCHXM,TYSHXYDM)
in(select fdmchxm,TYSHXYDM FROM TEST.CF GROUP BY FDMCHXM,TYSHXYDM HAVING COUNT(1)=1)
+重复数据总量