通过SQL保留数据库表中的唯一一行重复数据

在项目开发和维护过程中，经常会遇到数据库中存在重复数据，由此带来一系列的问题。于是“如何删除重复数据，并保留重复数据的一条记录”这样的需求就会经常在工作中遇到。而每次遇到这样的问题总是要重新整理sql，今天再次遇到这样的问题，决定还是记录下来，方便以后查阅。

 

目前工作的数据库是SQL Server 2008， 所以该方案只针对SQL Server有效。

大致思路： 通过Rank函数对可能存在重复值的columns进行分块Ranking （如下表）。随后对rank值大于1的记录进行删除。

ProductID   Name                   LocationID   Quantity Rank
----------- ---------------------- ------------ -------- ----
494         Paint - Silver         3            49       1
495         Paint - Blue           3            49       1
493         Paint - Red            3            41       3
496         Paint - Yellow         3            30       4
492         Paint - Black          3            17       5
495         Paint - Blue           4            35       1
496         Paint - Yellow         4            25       2
493         Paint - Red            4            24       3
492         Paint - Black          4            14       4
494         Paint - Silver         4            12       5

大致的SQL 如下：

 

alter table tableName add  sno int identity(1,1)
delete from tableName where sno in
(
	select sno from (
		select *,
		RANK() OVER ( PARTITION BY empid,name ORDER BY sno DESC )rank
		From tableName
	)T
	where rank>1
)
ALTER TABLE tableName DROP COLUMN sno

 当然还有其他方法，诸如通过临时表之类的，下次用到再补充。

 

针对Oracle，可以通过rowid来实现相同的功能，示例如下：

DELETEFROM table WHERE rowid notin(SELECT MIN(rowid)FROM table GROUPBY column1, column2, column3);