从表中删除重复项

Question

数据库类型是PostGres 8.3。

如果我写：

SELECT field1, field2, field3, count(*) 
FROM table1
GROUP BY field1, field2, field3 having count(*) > 1;

我有一些行的计数超过1。如何去除重复项（我仍然希望每个重复项只保留1行，而不是+1行...我不想全部删除）

示例：

1-2-3
1-2-3
1-2-3
2-3-4
4-5-6

Should become :

1-2-3
2-3-4
4-5-6

我找到的唯一答案在这里，但我想知道是否可以不使用哈希列来完成。

警告我没有带有唯一编号的PK，因此无法使用min(...)技术。PK是3个字段。

- Daok

---

我不确定我是否理解正确。你说“PK是3个字段”-那么为什么表中有重复项，比如多个1-2-3的记录。如果我理解有误请纠正我。 - Vijay Dev

PK在3个字段中，我们必须将它们移除以进行合并（长话短说），现在我们需要把它放回去。我们有一些重复的内容，我们想要删除它们。 - Patrick Desjardins

Answer 1

这是所有表格都应该有主键的原因之一（不一定是ID号或IDENTITY，但是必须是一个或多个列的组合，可以唯一标识一行并在数据库中强制实施其唯一性）。

最好的选择是像这样：

SELECT field1, field2, field3, count(*) 
INTO temp_table1
FROM table1
GROUP BY field1, field2, field3 having count(*) > 1

DELETE T1
FROM table1 T1
INNER JOIN (SELECT field1, field2, field3
      FROM table1
      GROUP BY field1, field2, field3 having count(*) > 1) SQ ON
            SQ.field1 = T1.field1 AND
            SQ.field2 = T1.field2 AND
            SQ.field3 = T1.field3

INSERT INTO table1 (field1, field2, field3)
SELECT field1, field2, field3
FROM temp_table1

DROP TABLE temp_table1

- Tom H.

---

我目前正在尝试您的建议。+1 - Patrick Desjardins

它有效，谢谢。我修改了一些东西，比如DELETE T1不起作用，我必须在FROM table1 as T1之后放置T1。像这样的几个问题。但解决方案很快也很成功。 - Patrick Desjardins

很高兴它对你有用。感谢指出我忽略了T1别名。我已经在脚本中进行了更正。 - Tom H

Answer 2

一个可能的答案是：

CREATE <temporary table> (<correct structure for table being cleaned>);
BEGIN WORK;   -- if needed
INSERT INTO <temporary table> SELECT DISTINCT * FROM <source table>;
DELETE FROM <source table>
INSERT INTO <source table> SELECT * FROM <temporary table>;
COMMIT WORK;  -- needed
DROP <temporary table>;

我不确定在事务语句中是否需要使用“work”，也不确定在PostgreSQL中是否需要显式的BEGIN。但是这个概念适用于任何DBMS。

唯一需要注意的是参照约束，特别是触发删除操作。如果存在这些操作，可能会导致效果不佳。

- Jonathan Leffler

Answer 3

如果表格是使用OID对象ID创建的，则将使用它：

DELETE FROM table1
WHERE OID NOT IN (SELECT   MIN (OID)
                              FROM table1
                          GROUP BY field1, field2, field3)

- Brian Schmitt

Answer 4

使用TSQL，不确定Postgres是否支持临时表，但您可以选择到一个临时表中，然后循环遍历并删除和插入结果回到原始表中。

-- **Disclaimer** using TSQL
-- You could select your records into a temp table with a pk
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 -- You could take these results and pump them back into --your original table

Drop Table #dupes

我在 MS SQL Server 2000 上测试过这个。虽然不熟悉 Postgres 的选项，但也许这会指引你朝正确的方向前进。

- bnkdev

Answer 5

这是我找到的最简单的方法：

Postgre SQL 语法：

CREATE TABLE tmp AS SELECT distinct * FROM table1
truncate table table1
insert into table1 select * from tmp
drop table tmp

T-SQL 语法：

select distinct * into #tmp from table1
truncate table table1
insert into table1 select * from #tmp
drop table #tmp

- Aaron Palmer

Answer 6

可能我理解错了什么，但我会说：

从table1中选择 DISTINCT field1、field2和field3。

太简单了，难道不好吗？^^

- Guillaume

---

我认为Daok想要删除除一个之外的所有重复条目。 - Vijay Dev

Answer 7

这个问题有一个很好的答案，但是针对的是SQL Server。它使用了SQL Server提供的ROWCOUNT，效果很好。我从来没有使用过PostgreSQL，因此不知道PostgreSQL中ROWCOUNT的等价物。

- Vijay Dev