代码之家 › 专栏 › 技术社区 › Patrick Desjardins

从表中删除重复项

postgresql sql

Patrick Desjardins · 技术社区 · 17 年前

数据库类型为Postgres 8.3。

如果我写道:

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

我有一些行的计数超过1。如何取出副本(我仍然希望每个副本有一行,而不是+1行…我不想全部删除。)

例子:

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

应该成为:

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

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

警告我没有一个具有唯一数字的pk,因此不能使用min(…)技术。pk是3个字段。

7 回复 | 直到 11 年前

Tom H zenazn 17 年前

这是所有表都应该具有主键的许多原因之一(不一定是ID号或标识,而是一个或多个列的组合,这些列唯一标识行,并且在数据库中强制其唯一性)。

您的最佳选择如下:

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

Jonathan Leffler 17 年前

一个可能的答案是:

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。

唯一要注意的是引用约束,尤其是触发的删除操作。如果这些存在,这可能会证明不太令人满意。

Brian Schmitt 17 年前

这将使用 OID 对象ID(如果表是用它创建的):

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

Guillaume 17 年前

嗯,我应该误解一些事情,但我会说:

选择 独特的 表1中的field1、field2、field3

太容易做好人?^ ^

Aaron Palmer 17 年前

这是我发现的最简单的方法:

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

a_horse_with_no_name 11 年前

好的 Answer 针对此问题,但针对SQL Server。它使用了SQL Server提供的行数,效果很好。我从未使用过postgresql,因此不知道postgresql中的rowcount等价物。

a_horse_with_no_name 11 年前

使用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的选择,但这可能会引导您朝着正确的方向前进。