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

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

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

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

Чтобы сохранить 9X_tsql последнюю версию в порядке RowID, например, вы 9X_ms-sql-server можете использовать ORDER BY RowID DESC

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

План выполнения для 9X_ms-sql-server этого часто проще и эффективнее, чем в принятом 9X_duplicate-removal ответе, поскольку он не требует самостоятельного 9X_mssql соединения.

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

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

Факторы, которые могут способствовать 9X_tsql использованию агрегированного хеш-метода:

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

В 9X_no-duplicates крайних вариантах этого второго случая (если 9X_transact-sql очень мало групп с большим количеством дубликатов 9X_duplicate в каждой) можно также рассмотреть возможность 9X_ms-sql-server простой вставки строк, которые нужно сохранить 9X_sql-server в новую таблицу, а затем TRUNCATE - вставить оригинал 9X_duplicate-removal и скопировать их обратно в свести к минимуму 9X_sql-server ведение журнала по сравнению с удалением 9X_duplicate-data очень большой части строк.

На сайте поддержки Microsoft есть хорошая 9X_no-duplicates статья о removing duplicates. Это довольно консервативно — они 9X_deduplication заставляют вас делать все отдельными шагами 9X_ms-sql-server — но это должно хорошо работать с большими 9X_duplicates таблицами.

В прошлом я использовал для этого 9X_duplicate самообъединения, хотя это, вероятно, можно 9X_tsql было бы украсить предложением HAVING:

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

Следующий запрос полезен для удаления повторяющихся 9X_mssql строк. Таблица в этом примере имеет ID в качестве 9X_transact-sql столбца идентификаторов, а столбцы с повторяющимися 9X_ms-sql-server данными - это 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) 

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

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

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

9X_duplicate-removal

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

Это удалит повторяющиеся строки, кроме первой 9X_duplication строки

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 для удаления повторяющихся 9X_tsql строк из таблицы сервера sql

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

с 9X_mssql сохранением оригинала

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

9X_transact-sql

Быстрое и грязное удаление точно повторяющихся 9X_sql-srever строк (для небольших таблиц):

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

Я предпочитаю решение подзапроса \ с count 9X_deduplication (*)> 1 внутреннему соединению, потому что 9X_duplicate-removal мне было легче читать, и было очень легко 9X_duplicate превратить его в оператор SELECT, чтобы 9X_duplication проверить, что будет удалено, перед его 9X_tsql запуском.

--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 tempdb.dbo.tmpTable FROM myTable TRUNCATE TABLE myTable INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable DROP TABLE tempdb.dbo.tmpTable 
9X_duplicates

Я решил поделиться своим решением, поскольку 9X_ms-sql-server оно работает в особых обстоятельствах. В 9X_duplicates моем случае таблица с повторяющимися значениями 9X_duplicate не имела внешнего ключа (потому что значения 9X_sql-srever были дублированы из другой базы данных).

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: при 9X_transact-sql работе с подобными вещами я всегда использую 9X_ms-sql-server транзакцию, это не только гарантирует, что 9X_sql-server все выполняется как единое целое, но и позволяет 9X_sql-server мне тестировать, ничем не рискуя. Но, конечно, вы 9X_duplicate все равно должны сделать резервную копию, чтобы 9X_deduplication быть уверенным ...

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

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

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

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

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

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

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

Еще одно простое решение можно найти по 9X_duplicates вставленной ссылке here. Его легко понять, и 9X_duplicates он, кажется, эффективен для большинства 9X_duplicate подобных проблем. Хотя он предназначен для 9X_transact-sql SQL Server, но использованная концепция 9X_duplicate-removal более чем приемлема.

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

Учтите эти данные:

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 

Итак, как 9X_ms-sql-server мы можем удалить эти повторяющиеся данные?

Сначала 9X_no-duplicates вставьте столбец идентификаторов в эту таблицу, используя 9X_duplicate-removal следующий код:

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

Используйте следующий код, чтобы 9X_transact-sql решить эту проблему:

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

Используйте это

WITH tblTemp as ( SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name) As RowNumber,* FROM ) DELETE FROM tblTemp where RowNumber >1 

9X_mssql

У меня была таблица, в которой мне нужно 9X_deduplication было сохранить неповторяющиеся строки. Я 9X_duplicate-removal не уверен в скорости или эффективности.

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

Вот еще одна хорошая статья о removing duplicates.

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

Решение для временной таблицы 9X_sql-srever и два примера MySQL.

Собираетесь ли вы в 9X_transact-sql будущем предотвратить это на уровне базы 9X_ms-sql-server данных или с точки зрения приложения. Я 9X_transact-sql бы предложил уровень базы данных, потому 9X_sql-srever что ваша база данных должна отвечать за 9X_no-duplicates поддержание ссылочной целостности, разработчики 9X_duplication просто вызовут проблемы;)

О, конечно. Используйте временную таблицу. Если 9X_duplicate-removal вам нужно одно, не очень эффективное утверждение, которое 9X_duplication «работает», вы можете использовать:

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) 

По сути, для 9X_duplicate-removal каждой строки в таблице вложенный выбор 9X_tsql находит верхний RowID всех строк, которые 9X_sql-srever в точности совпадают с рассматриваемой строкой. Таким 9X_tsql образом, вы получите список идентификаторов 9X_deduplication RowID, представляющих «исходные» неповторяющиеся 9X_no-duplicates строки.

Другой способ - создать новую таблицу с такими же полями 9X_deduplication и с уникальным индексом. Затем переместите все данные из старой таблицы в новую. SQL SERVER автоматически игнорирует 9X_duplication (есть также вариант, что делать, если будет 9X_mssql повторяющееся значение: игнорировать, прерывание 9X_duplicate-removal или sth) повторяющиеся значения. Итак, у 9X_duplication нас есть та же таблица без повторяющихся 9X_mssql строк. Если вам не нужен уникальный индекс, после передачи данных его можно удалить.

Особенно для больших таблиц вы можете использовать 9X_deduplication DTS (пакет SSIS для импорта / экспорта данных), чтобы 9X_duplicate-removal быстро перенести все данные в вашу новую 9X_transact-sql уникально проиндексированную таблицу. Для 9X_duplicates 7 миллионов строк требуется всего несколько 9X_sql-server минут.

Используя приведенный ниже запрос, мы можем 9X_tsql удалить повторяющиеся записи на основе одного 9X_duplicates или нескольких столбцов. ниже запрос удаляется 9X_duplicate-removal на основе двух столбцов. имя таблицы: testing, а 9X_sql-srever имена столбцов 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) 

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

2. Выполнить 9X_duplication такой запрос

   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 

Другой способ сделать это: -

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

9X_duplicates

Я бы упомянул об этом подходе, поскольку 9X_no-duplicates он может быть полезным и работает на всех 9X_sql-srever серверах SQL: Довольно часто дубликатов 9X_duplication всего один - два, а идентификаторы и количество 9X_tsql дубликатов известны. В этом случае:

SET ROWCOUNT 1 -- or set to number of rows to be deleted delete from myTable where RowId = DuplicatedID SET ROWCOUNT 0 

С прикладного уровня (к сожалению). Я согласен 9X_duplication с тем, что правильный способ предотвращения 9X_duplicate-removal дублирования — это использование уникального 9X_deduplication индекса на уровне базы данных, но в SQL 9X_mssql Server 2005 индексу разрешено иметь размер 9X_ms-sql-server всего 900 байт, а мое поле varchar(2048) сбрасывает 9X_duplicate-data его со счетов.

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

-- 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) кажется 9X_duplicate мне подозрительным (некоторые вещи в жизни 9X_transact-sql занимают 2048 байт, но это довольно редко); это 9X_duplicate действительно не должно быть varchar (max)?

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

CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int) INSERT INTO car(PersonId,CarId) VALUES(1,2),(1,3),(1,2),(2,4) --SELECT * FROM car ;WITH CTE as( SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car) DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1) 
9X_deduplication

Я хочу предварительно просмотреть строки, которые 9X_deduplication вы собираетесь удалить, и контролировать, какие 9X_tsql из повторяющихся строк оставить. См. http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

with MYCTE as ( SELECT ROW_NUMBER() OVER ( PARTITION BY DuplicateKey1 ,DuplicateKey2 -- optional ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed ) RN FROM MyTable ) DELETE FROM MYCTE WHERE RN > 1 

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

alter table MyTable add sno int identity(1,1) delete from MyTable where sno in ( select sno from ( select *, RANK() OVER ( PARTITION BY RowID,Col3 ORDER BY sno DESC )rank From MyTable )T where rank>1 ) alter table MyTable drop column sno 
9X_tsql

Иногда используется механизм мягкого удаления, когда 9X_duplicate дата записывается, чтобы указать дату удаления. В 9X_deduplication этом случае оператор UPDATE может использоваться 9X_duplicate для обновления этого поля на основе повторяющихся 9X_duplication записей.

UPDATE MY_TABLE SET DELETED = getDate() WHERE TABLE_ID IN ( SELECT x.TABLE_ID FROM MY_TABLE x JOIN (SELECT min(TABLE_ID) id, COL_1, COL_2, COL_3 FROM MY_TABLE d GROUP BY d.COL_1, d.COL_2, d.COL_3 HAVING count(*) > 1) AS d ON d.COL_1 = x.COL_1 AND d.COL_2 = x.COL_2 AND d.COL_3 = x.COL_3 AND d.TABLE_ID <> x.TABLE_ID /*WHERE x.COL_4 <> 'D' -- Additional filter*/) 

Этот метод хорошо послужил мне для 9X_duplicates довольно умеренных таблиц, содержащих ~ 30 9X_deduplication миллионов строк с большим и низким количеством 9X_transact-sql дубликатов.

Я знаю, что на этот вопрос уже дан ответ, но 9X_duplicate-removal я создал довольно полезный sp, который создаст 9X_duplicate-data оператор динамического удаления для дубликатов 9X_mssql таблицы:

 CREATE PROCEDURE sp_DeleteDuplicate @tableName varchar(100), @DebugMode int =1 AS BEGIN SET NOCOUNT ON; IF(OBJECT_ID('tempdb..#tableMatrix') is not null) DROP TABLE #tableMatrix; SELECT ROW_NUMBER() OVER(ORDER BY name) as rn,name into #tableMatrix FROM sys.columns where [object_id] = object_id(@tableName) ORDER BY name DECLARE @MaxRow int = (SELECT MAX(rn) from #tableMatrix) IF(@MaxRow is null) RAISERROR ('I wasn''t able to find any columns for this table!',16,1) ELSE BEGIN DECLARE @i int =1 DECLARE @Columns Varchar(max) =''; WHILE (@i <= @MaxRow) BEGIN SET @Columns=@Columns+(SELECT '['+name+'],' from #tableMatrix where rn = @i) SET @i = @i+1; END ---DELETE LAST comma SET @Columns = LEFT(@Columns,LEN(@Columns)-1) DECLARE @Sql nvarchar(max) = ' WITH cteRowsToDelte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY '+@Columns+' ORDER BY ( SELECT 0)) as rowNumber,* FROM '+@tableName +') DELETE FROM cteRowsToDelte WHERE rowNumber > 1; ' SET NOCOUNT OFF; IF(@DebugMode = 1) SELECT @Sql ELSE EXEC sp_executesql @Sql END END 

Итак, если вы создадите такую ​​таблицу:

IF(OBJECT_ID('MyLitleTable') is not null) DROP TABLE MyLitleTable CREATE TABLE MyLitleTable ( A Varchar(10), B money, C int ) --------------------------------------------------------- INSERT INTO MyLitleTable VALUES ('ABC',100,1), ('ABC',100,1), -- only this row should be deleted ('ABC',101,1), ('ABC',100,2), ('ABCD',100,1) ----------------------------------------------------------- exec sp_DeleteDuplicate 'MyLitleTable',0 

Он 9X_duplicate-removal удалит все дубликаты из вашей таблицы. Если 9X_duplicate вы запустите его без второго параметра, он 9X_tsql вернет для запуска инструкцию SQL.

Если вам 9X_transact-sql нужно исключить какой-либо столбец, просто 9X_mssql запустите его в режиме отладки, получите 9X_duplicate код и измените его как хотите.

Если все столбцы в повторяющихся строках 9X_mssql одинаковы, то для удаления повторяющихся 9X_duplicate записей можно использовать приведенный ниже 9X_sql-srever запрос.

SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable TRUNCATE TABLE #OriginalTable INSERT INTO #OriginalTable SELECT * FROM #TemNewTable DROP TABLE #TemNewTable 

Для структуры таблицы

MyTable

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

Запрос на удаление 9X_ms-sql-server дубликатов:

DELETE t1 FROM MyTable t1 INNER JOIN MyTable t2 WHERE t1.RowID > t2.RowID AND t1.Col1 = t2.Col1 AND t1.Col2=t2.Col2 AND t1.Col3=t2.Col3; 

Я предполагаю, что RowID - это своего 9X_sql-server рода автоинкремент, а остальные столбцы 9X_duplicates имеют повторяющиеся значения.