Вопрос: Как удалить повторяющиеся строки?


Каков наилучший способ удаления повторяющихся строк из довольно больших 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

1154


источник


Ответы:


Если не считать нулей, вы GROUP BYуникальные столбцы и SELECT MIN (or MAX)RowId в качестве строки для сохранения. Затем просто удалите все, у которых не было идентификатора строки:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

Если у вас есть идентификатор GUID вместо целого числа, вы можете заменить

MIN(RowId)

с

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

1058



Другим возможным способом

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

я использую ORDER BY (SELECT 0)выше, поскольку он произволен, какая строка сохраняется в случае равенства.

Чтобы сохранить последнюю в RowIDнапример, вы можете использовать ORDER BY RowID DESC

Планы выполнения

План выполнения для этого часто проще и эффективнее, чем в принятом ответе, поскольку он не требует самостоятельного присоединения.

Execution Plans

Однако это не всегда так. Одно место, где GROUP BYрешение может быть предпочтительным, это ситуации, когда хэш-агрегат будет выбираться предпочтительнее агрегата потока.

ROW_NUMBERрешение всегда будет давать практически такой же план, тогда как GROUP BYстратегия более гибкая.

Execution Plans

Факторы, которые могли бы поддержать хеш-агрегатный подход, были бы

  • Нет полезного индекса в столбцах разбиения
  • относительно меньше групп с относительно большим количеством дубликатов в каждой группе

В экстремальных версиях этого второго случая (если в каждом из них очень мало групп со многими дубликатами), можно также рассмотреть возможность просто вставить строки в новую таблицу, тогда TRUNCATE- вернуть оригинал и скопировать их обратно, чтобы свести к минимуму ведение журнала по сравнению с удалением очень большой доли строк.


692



Есть хорошая статья о удаление дубликатов на сайте поддержки Microsoft. Это довольно консервативно - они делают все по-отдельности, но должны хорошо работать против больших таблиц.

Я использовал self-join, чтобы сделать это в прошлом, хотя, вероятно, он был бы преувещен предложением HAVING:

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

130



Следующий запрос полезен для удаления повторяющихся строк. Таблица в этом примере IDв качестве столбца идентификации и столбцов, которые имеют повторяющиеся данные, являются Column1, Column2а также Column3,

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

Следующий сценарий показывает использование GROUP BY, HAVING, ORDER BYв одном запросе, и возвращает результаты с повторяющимся столбцом и его счетчиком.

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 

86



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

Postgres:

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

52



DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 

41



This will delete duplicate rows, except the first row

DELETE
FROM
    Mytable
WHERE
    RowID NOT IN (
        SELECT
            MIN(RowID)
        FROM
            Mytable
        GROUP BY
            Col1,
            Col2,
            Col3
    )

Refer (http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server)


36



I would prefer CTE for deleting duplicate rows from sql server table

strongly recommend to follow this article ::http://dotnetmob.com/sql-server-article/delete-duplicate-rows-in-sql-server/

by keeping original

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

without keeping original

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

22



Quick and Dirty to delete exact duplicated rows (for small tables):

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

21