数据库删除重复记录只保留一条方法归纳

1.需求分析

Person表包含PersonID，PersonName，Age；PersonID和PersonName字段出现重复值，需要保留其中一条

2.解决方法

2.1 ORACLE

使用rowid区分重复数据

delete from Person a
where (a.PersonID,a.PersonName,Age) in (
	select a.PersonID,a.PersonName,Age from Person 
		group by a.PersonID,a.PersonName,Age 
		having count(*) > 1)
and rowid not in (
	select min(rowid) from Person 
		group by a.PersonID,a.PersonName,Age 
		having count(*)>1);

2.2 HANA

对于未建主键的表出现重复记录需要删除时，可以利用"$rowid$"进行区分并做处理

delete from Person a WHERE (a.PersonID,a.PersonName,Age) IN (
	SELECT a.PersonID,a.PersonName,Age FROM Person 
	GROUP BY a.PersonID,a.PersonName,Age 
	HAVING COUNT(*) > 1)
AND "$rowid$" NOT IN
 (SELECT MIN("$rowid$") FROM Person 
 	GROUP BY a.PersonID,a.PersonName,Age 
 	HAVING COUNT(*) > 1);

2.3 SQL Server

与上述三种数据库不一样的地方，此处需要PersonID为唯一值（如自增字段）

DELETE  FROM Person 
WHERE  EXISTS(
	SELECT * FROM (
		SELECT MAX(PersonID) PersonID,PersonName,Age FROM dbo.Person
		GROUP BY PersonName,Age
		HAVING COUNT(1)>1) T
	WHERE Person.PersonName=T.PersonName 
		AND Person.Age=T.Age
		AND Person.PersonID<T.PersonID--如果上面使用MIN函数，这里就要改成>
)

参考案例：案例分享 | SQL Server删除重复数据只保留一行

2.4 MySQL

与SQL Server数据库一样，此处需要PersonID为唯一值（如自增字段）

DELETE  FROM Person 
WHERE  EXISTS(
	SELECT * FROM (
		SELECT MAX(PersonID) PersonID,PersonName,Age FROM dbo.Person
		GROUP BY PersonName,Age
		HAVING COUNT(1)>1) T
	WHERE Person.PersonName=T.PersonName 
		AND Person.Age=T.Age
		AND Person.PersonID<T.PersonID--如果上面使用MIN函数，这里就要改成>
)