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

Предполагая, что нет нулей, вы 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)))

Еще одним возможным способом сделать это является

; 

--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

план исполнения

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

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

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

Факторами, которые могли бы способствовать применению агрегированного подхода hash, являются:

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

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

На сайте Службы Поддержки Майкрософт есть хорошая статья об удалении дубликатов . Это довольно консервативно - они заставляют вас делать все по отдельности, но это должно хорошо работать против больших столов.

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

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

Следующий запрос полезен для удаления повторяющихся строк. Таблица в этом примере содержит столбец идентификаторов 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 

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

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

Это приведет к удалению повторяющихся строк, за исключением первой строки

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

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

Я бы предпочел CTE для удаления дубликатов строк из таблицы сервера sql

настоятельно рекомендую следовать этой статье :: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

сохраняя оригинальный

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

без сохранения оригинала

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)

Быстрое и грязное удаление точных дублированных строк (для небольших таблиц):

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

Я предпочитаю решение subquery\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
name, email, COUNT(*)
FROM 
users
GROUP BY
name, email
HAVING COUNT(*) > 1

Чтобы удалить повторяющиеся строки:

DELETE users 
WHERE rowid NOT IN 
(SELECT MIN(rowid)
FROM users
GROUP BY name, email);      

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable

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

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS: при работе с такими вещами я всегда использую транзакцию, это не только гарантирует, что все будет выполнено в целом, но и позволяет мне тестировать, не рискуя ничем. Но, конечно, вы все равно должны взять резервную копию, просто чтобы быть уверенными...

Используя CTE. Идея состоит в том, чтобы объединить один или несколько столбцов, которые образуют дублирующую запись, а затем удалить то, что вам нравится:

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;

Еще одно простое решение можно найти по ссылке, вставленной здесь . Это легко понять и, кажется, эффективно для большинства подобных проблем. Это для SQL сервера, хотя, но используемая концепция более чем приемлема.

Вот соответствующие части из связанной страницы:

Рассмотрим эти данные:

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

Так как же мы можем удалить эти дубликаты данных?

Во-первых, вставьте столбец идентификаторов в эту таблицу, используя следующий код:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

Используйте следующий код для его разрешения:

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

Этот запрос показал очень хорошую производительность для меня:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

он удалил 1M строк чуть более чем за 30 секунд из таблицы 2M (50% дубликатов)

Вот еще одна хорошая статья по удалению дубликатов .

Он обсуждает, почему его трудно: "SQL основан на реляционной алгебре, и дубликаты не могут встречаться в реляционной алгебре, потому что дубликаты не допускаются в множестве. "

Решение временной таблицы и два примера mysql.

В будущем вы собираетесь предотвратить это на уровне базы данных или с точки зрения приложения. Я бы предложил уровень базы данных, потому что ваша база данных должна отвечать за поддержание ссылочной целостности, разработчики просто вызовут проблемы ;)

Ну конечно. Используйте временную таблицу. Если вы хотите один, not-very-performant оператор, что "works" вы можете пойти с:

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

В принципе, для каждой строки в таблице поднабор находит верхнюю RowID из всех строк, которые точно похожи на рассматриваемую строку. Таким образом, вы получаете список RowIDs, которые представляют собой "original" недублированных строк.

У меня была таблица, в которой мне нужно было сохранить недублированные строки. Я не уверен в скорости или эффективности.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

Другой способ- создать новую таблицу с теми же полями и с уникальным индексом . Затем переместите все данные из старой таблицы в новую. Автоматически SQL SERVER ignore (есть также опция о том, что делать, если будет Дублированное значение: ignore, interrupt или sth) дублирует значения. Таким образом, мы имеем одну и ту же таблицу без повторяющихся строк. Если вам не нужен уникальный индекс, то после передачи данных вы можете его отбросить .

Специально для больших таблиц вы можете использовать DTS (пакет SSIS для импорта / экспорта данных), чтобы быстро перенести все данные в вашу новую уникально индексированную таблицу. Для 7-миллионного ряда это занимает всего несколько минут.

Использовать это

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

1. Создайте новую пустую таблицу с той же структурой

2. Выполните запрос следующим образом

   INSERT INTO tc_category1
   SELECT *
   FROM tc_category
   GROUP BY category_id, application_id
   HAVING count(*) > 1
   

3. Затем выполните этот запрос

   INSERT INTO tc_category1
   SELECT *
   FROM tc_category
   GROUP BY category_id, application_id
   HAVING count(*) = 1
   

Используя приведенный ниже запрос, мы можем удалить дубликаты записей на основе одного столбца или нескольких столбцов. ниже запрос удаляется на основе двух столбцов. имя таблицы: testing и имена столбцов empno,empname

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

Это самый простой способ удалить дублирующуюся запись

 DELETE FROM tblemp WHERE id IN 
 (
  SELECT MIN(id) FROM tblemp
   GROUP BY  title HAVING COUNT(id)>1
 )

http://askme.indianyouth.info/details/how-to-dumplicate-record-from-table-in-using-sql-105

С уровня приложения (к сожалению). Я согласен, что правильный способ предотвратить дублирование - это использовать уникальный индекс на уровне базы данных, но в SQL Server 2005 индекс может быть только 900 байт, и мое поле varchar(2048) сдувает это.

Я не знаю, насколько хорошо это сработает, но я думаю, что вы могли бы написать триггер для обеспечения этого, даже если вы не можете сделать это непосредственно с индексом. Что-то вроде:

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

Кроме того, varchar (2048) звучит подозрительно для меня (некоторые вещи в жизни составляют 2048 байт, но это довольно необычно); действительно ли это не должно быть varchar (max)?