使用TSQL,不确定Postgres是否支持临时表,但您可以选择到一个临时表中,然后循环遍历并删除和插入结果回到原始表中。
Create Table #dupes
([id] int not null identity(1,1), f1 int, f2 int, f3 int)
Insert Into #dupes (f1,f2,f3) values (1,2,3)
Insert Into #dupes (f1,f2,f3) values (1,2,3)
Insert Into #dupes (f1,f2,f3) values (1,2,3)
Insert Into #dupes (f1,f2,f3) values (2,3,4)
Insert Into #dupes (f1,f2,f3) values (4,5,6)
Insert Into #dupes (f1,f2,f3) values (4,5,6)
Insert Into #dupes (f1,f2,f3) values (4,5,6)
Insert Into #dupes (f1,f2,f3) values (7,8,9)
Select f1,f2,f3 From #dupes
Declare @rowCount int
Declare @counter int
Set @counter = 1
Set @rowCount = (Select Count([id]) from #dupes)
while (@counter < @rowCount + 1)
Begin
Delete From #dupes
Where [Id] <>
(Select [id] From #dupes where [id]=@counter)
and
(
[f1] = (Select [f1] from #dupes where [id]=@counter)
and
[f2] = (Select [f2] from #dupes where [id]=@counter)
and
[f3] = (Select [f3] from #dupes where [id]=@counter)
)
Set @counter = @counter + 1
End
Select f1,f2,f3 From #dupes
Drop Table #dupes
我在 MS SQL Server 2000 上测试过这个。虽然不熟悉 Postgres 的选项,但也许这会指引你朝正确的方向前进。