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

Добавьте столбец идентификации, который будет выступать в качестве суррогатного первичного ключа, и используйте его для идентификации двух из трех строк, которые необходимо удалить.

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

Я бы ВЫБРАЛ РАЗЛИЧНЫЕ строки и бросил их во временную таблицу, затем отбросил исходную таблицу и скопировал данные из временного хранилища. Обновлено: теперь с фрагментом кода!

INSERT INTO TABLE_2 
SELECT DISTINCT * FROM TABLE_1
GO
DELETE FROM TABLE_1
GO
INSERT INTO TABLE_1
SELECT * FROM TABLE_2
GO

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

Следующий пример также работает, когда ваш ПК - это всего лишь подмножество всех столбцов таблицы.

(Примечание: мне больше нравится подход со вставкой еще одного столбца суррогатного идентификатора. Но, возможно, это решение также пригодится.)

Сначала найдите повторяющиеся строки:

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Если их мало, вы можете удалить их вручную:

set rowcount 1
delete from t1
where col1=1 and col2=1

Значение rowcount должно быть в n-1 раз больше, чем количество дубликатов. В этом примере есть 2 дубликата, поэтому количество строк равно 1. Если вы получаете несколько повторяющихся строк, вы должны делать это для каждого уникального первичного ключа.

Если у вас много дубликатов, скопируйте каждый ключ один раз в другую таблицу:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

Затем скопируйте ключи, но удалите дубликаты.

SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

В ваших ключах теперь есть уникальные ключи. Проверьте, нет ли результата:

SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

Удалите дубликаты из исходной таблицы:

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

Вставьте исходные строки:

INSERT t1 SELECT * FROM holddups

кстати, и для полноты: в Oracle есть скрытое поле, которое вы можете использовать (rowid):

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

см .: Сайт знаний Майкрософт

После того, как вы очистите текущий беспорядок, вы можете добавить первичный ключ, который включает все поля в таблице. это не даст вам снова попасть в беспорядок. Конечно, это решение вполне может нарушить существующий код. С этим тоже придется разобраться.

Я не уверен, работает ли это с операторами DELETE, но это способ найти повторяющиеся строки:

 SELECT *
 FROM myTable t1, myTable t2
 WHERE t1.field = t2.field AND t1.id > t2.id

Я не уверен, что вы можете просто изменить «SELECT» на «DELETE» (кто-то хочет дать мне знать?), но даже если вы не можете, вы можете просто превратить его в подзапрос.

Вот метод, который я использовал когда я задал этот вопрос -

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

Можете ли вы добавить в таблицу поле идентификации первичного ключа?

Манрико Корацци - Я специализируюсь на Oracle, а не на MS SQL, поэтому вам придется сказать мне, возможно ли это для повышения производительности: -

1. Оставьте то же самое, что и ваш первый шаг - вставьте различные значения в ТАБЛИЦУ2 из ТАБЛИЦЫ1.
2. Отбросьте ТАБЛИЦУ1. (Я полагаю, что удаление должно быть быстрее, чем удаление, так же как усечение быстрее, чем удаление).
3. Переименуйте TABLE2 в TABLE1 (экономит ваше время, поскольку вы переименовываете объект, а не копируете данные из одной таблицы в другую).

Вот другой способ, с тестовыми данными

create table #table1 (colWithDupes1 int, colWithDupes2 int)
insert into #table1
(colWithDupes1, colWithDupes2)
Select 1, 2 union all
Select 1, 2 union all
Select 2, 2 union all
Select 3, 4 union all
Select 3, 4 union all
Select 3, 4 union all
Select 4, 2 union all
Select 4, 2 


select * from #table1

set rowcount 1
select 1

while @@rowcount > 0
delete #table1  where 1 < (select count(*) from #table1 a2 
   where #table1.colWithDupes1 = a2.colWithDupes1
and #table1.colWithDupes2 = a2.colWithDupes2
)

set rowcount 0

select * from #table1

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

На основе Эта статья.

CREATE TABLE #temp (i INT)

INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (1)
INSERT INTO #temp VALUES (2)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (3)
INSERT INTO #temp VALUES (4)

SELECT * FROM #temp

;
WITH [#temp+rowid] AS
(SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS ROWID, * FROM #temp)
DELETE FROM [#temp+rowid] WHERE rowid IN 
(SELECT MIN(rowid) FROM [#temp+rowid] GROUP BY i HAVING COUNT(*) > 1)

SELECT * FROM #temp

DROP TABLE #temp   

Как насчет:

select distinct * into #t from duplicates_tbl

truncate duplicates_tbl

insert duplicates_tbl select * from #t

drop table #t

Что насчет этого решения:

Сначала вы выполняете следующий запрос:

  select 'set rowcount ' + convert(varchar,COUNT(*)-1) + ' delete from MyTable where field=''' + field +'''' + ' set rowcount 0'  from mytable group by field having COUNT(*)>1

И тогда вам просто нужно выполнить возвращенный набор результатов

set rowcount 3 delete from Mytable where field='foo' set rowcount 0
....
....
set rowcount 5 delete from Mytable where field='bar' set rowcount 0

Я рассмотрел случай, когда у вас есть только один столбец, но довольно легко адаптировать тот же подход к более чем одному столбцу. Дайте мне знать, если вы хотите, чтобы я опубликовал код.