我需要从一个相当大的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 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

其他回答

使用这个

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1
DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

创建具有相同结构的新空白表 像这样执行查询 插入tc_category1 SELECT * 从tc_category GROUP BY category_id, application_id count(*) > 然后执行这个查询 插入tc_category1 SELECT * 从tc_category GROUP BY category_id, application_id count(*) = 1

我更喜欢子查询\having count(*) > 1解决方案内部连接,因为我发现它更容易阅读,它很容易变成一个SELECT语句来验证什么将被删除,然后再运行它。

--DELETE FROM table1 
--WHERE id IN ( 
     SELECT MIN(id) FROM table1 
     GROUP BY col1, col2, col3 
     -- could add a WHERE clause here to further filter
     HAVING count(*) > 1
--)

快速和脏删除精确重复的行(小表):

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;