如何删除重复的行?

我需要从一个相当大的SQL Server表(即300,000+行)中删除重复的行。

当然，由于RowID标识字段的存在，这些行不会完全重复。

MyTable

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

我该怎么做呢?

当前回答

另一种表达方式:——

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD

2016-02-02 06:59:27

其他回答

DELETE 
FROM MyTable
WHERE NOT EXISTS (
              SELECT min(RowID)
              FROM Mytable
              WHERE (SELECT RowID 
                     FROM Mytable
                     GROUP BY Col1, Col2, Col3
                     ))
               );

2014-01-02 15:27:26

delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid

邮政：

delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid

2010-09-30 02:35:06

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

2013-10-03 06:18:06

创建另一个由原始值组成的表:

CREATE TABLE table2 AS SELECT *, COUNT(*) FROM table1 GROUP BY name HAVING COUNT (*) > 0

2022-10-07 22:09:19

在postgresql中删除重复行的一个非常简单的方法。

DELETE FROM table1 a
USING table1 b
WHERE a.id < b.id
AND a.column1 = b.column1
AND a.column2 = b.column2;

2021-04-30 19:24:30

如何删除重复的行?

推荐文章

最新文章

标签