DB2剔除重复数据的实现

DB2删除重复数据的实现
DB2删除重复数据使我们经常使用的操作，下面就教您DB2删除重复数据的方法，希望可以对您学习DB2删除重复数据方面有所帮助。
使用ROW_NUMBER 删除重复数据
假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

delete from (select * from (select a,b,c,row_number() over(partition by a,b,c order by a,b,c) as row_num from tab) as e where row_num >1) 

如果数据量太大可以采用如下方法：

Create table emp_profile_temp like emp_profile; 

大数据量采用

LOAD FROM CURSURDECLARE mycursor CURSOR FOR SELECT distinct * FROM emp_profile;  LOAD FROM mycursor OF CURSOR INSERT INTO emp_profile_temp; drop table emp_profile;  rename table emp_profile_temp to emp_profile  

DELETE FROM (SELECT ROWNUMBER() OVER (PARTITION BY column name) FROM table name) AS E(RN) WHERE RN > 1

--比如有一个表abc(a int,b varchar(10)), 字段b有重复

DELETE FROM     (SELECT ROWNUMBER() OVER (PARTITION BY b)       FROM abc) AS E(RN)       WHERE RN > 1

查看更多 下一篇