首页 > 代码库 > SQL-删除重复记录

SQL-删除重复记录

前几日工作的时候,有个小需求,是要求删除一个表table_A里的重复记录(保留一条),假设以字段COL_PK重复来判断记录重复,那么有几种写法:

在Oracle里,可以利用rowid来删除,这是非常高效的一种写法:

DELETE FROM TABLE_A  WHERE ROWID NOT IN (SELECT MIN(ROWID),COL_PK FROM TABLE_A  GROUP BY COL_PK); --首先是按COL_PK分组,找出最小的rowid,那么所有非最小值都是重复记录,删之即可。

不过可惜的是,我的开发环境是DB2数据库,db2数据库不能这么便利地利用rowid,但是db2数据可以利用row_number()函数,这点又是Oracle无法使用的:

DELETE FROM (SELECT ROW_NUMBER() OVER(PARTITION BY COL_PK ORDER BY COL_PK) AS NO FROM TABLE_A ) WHERE NO>1;

进一步对以上思路进行拓展:

如何删除所有的重复记录呢(不保留一条)?

Oracle:DELETE FROM TABLE_A WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM TABLE_A GROUP BY PK HAVING COUNT(1)=1)

DB2&ORACLE: DELETE FROM TABLE_A WHERE PK IN (SELECT PK FROM TABLE_A GROUP BY PK HAVING COUNT(1)>1)

SQL-删除重复记录