oracle 查重及处理(可保存最旧或最新数据)

查询所有重复数据的结果

SELECT ID,DWMC,ADD_TIME 
FROM 表名
WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) 

 

查询重复数据只保存,除了首条的结果

 

SELECT ID,DWMC,ADD_TIME 
FROM 表名
WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) 
AND  ADD_TIME NOT IN (
    SELECT MIN(ADD_TIME) FROM 表名 WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) GROUP BY DWMC
)

要保留最后一条的话，把MIN 改成MAX

删除重复数据，仅保存首条

 

delete from 表名 where id in (
    SELECT ID,DWMC,ADD_TIME 
    FROM 表名 
    WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) 
    AND  ADD_TIME NOT IN (
        SELECT MIN(ADD_TIME) FROM 表名 WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING   COUNT(DWMC) > 1) GROUP BY DWMC
    )
)

打上重复标签

update 表名 set DWMC=concat(DWMC,'(重复)') where id in (
    SELECT ID
    FROM 表名 
    WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) 
    AND  ADD_TIME NOT IN (
        SELECT MIN(ADD_TIME) FROM 表名  WHERE DWMC IN (SELECT DWMC FROM 表名 GROUP BY DWMC HAVING COUNT(DWMC) > 1) GROUP BY DWMC
    ) 
) AND DWMC NOT LIKE '%(重复)%'