Как я могу удалить дубликаты строк?

sql-server tsql duplicates

1163636 просмотра

30 ответа

37482 Репутация автора

Каков наилучший способ удалить повторяющиеся строки из довольно большой 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
Автор: Seibar Источник Размещён: 20.08.2008 09:51

Ответы (30)


144 плюса

40854 Репутация автора

На сайте поддержки Microsoft есть хорошая статья об удалении дубликатов . Это довольно консервативно - они заставляют вас делать все в отдельных шагах - но это должно хорошо работать с большими столами.

Я использовал для этого самостоятельные объединения в прошлом, хотя, вероятно, это можно было бы с помощью предложения HAVING:

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField
Автор: Jon Galloway Размещён: 20.08.2008 09:53

12 плюса

1301 Репутация автора

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

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

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

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

Автор: Craig Размещён: 20.08.2008 09:58

1116 плюса

77483 Репутация автора

Решение

Предполагая , что не аннулирует, вам 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)))
Автор: Mark Brackett Размещён: 20.08.2008 10:00

12 плюса

11401 Репутация автора

Да, конечно. Используйте временную таблицу. Если вам нужен один, не очень производительный оператор, который «работает», вы можете использовать:

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 всех строк, которые в точности соответствуют рассматриваемой строке. Таким образом, вы получите список RowID, которые представляют «оригинальные» недублированные строки.

Автор: Jacob Proffitt Размещён: 20.08.2008 10:27

7 плюса

15337 Репутация автора

С уровня приложения (к сожалению). Я согласен, что правильный способ предотвращения дублирования на уровне базы данных - использование уникального индекса, но в 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)?

Автор: DrPizza Размещён: 20.08.2008 10:53

9 плюса

0 Репутация автора

  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
    
Автор: Kamil Размещён: 08.05.2009 01:06

11 плюса

788 Репутация автора

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

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )
Автор: chrismar035 Размещён: 11.12.2009 01:47

738 плюса

362314 Репутация автора

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

; 

--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решение может быть предпочтительным, - это ситуации, когда хеш-агрегат будет выбран предпочтительнее, чем агрегат потока.

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

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

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

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

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

Автор: Martin Smith Размещён: 29.09.2010 02:52

59 плюса

6989 Репутация автора

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
Автор: SoftwareGeek Размещён: 30.09.2010 02:35

93 плюса

1951 Репутация автора

Следующий запрос полезен для удаления повторяющихся строк. Таблицы в этом примере , имеют в 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 
Автор: gngolakia Размещён: 23.11.2011 03:32

9 плюса

91 Репутация автора

Используя приведенный ниже запрос, мы можем удалить дубликаты записей на основе одного или нескольких столбцов. ниже запрос удаляется на основе двух столбцов. Имя таблицы: 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)
Автор: Sudhakar NV Размещён: 08.02.2012 12:06

6 плюса

10956 Репутация автора

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)
Автор: AnandPhadke Размещён: 11.07.2012 11:46

16 плюса

169 Репутация автора

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable
Автор: heta77 Размещён: 10.10.2012 11:17

7 плюса

119 Репутация автора

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

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
Автор: Evgueny Sedov Размещён: 30.01.2013 07:45

22 плюса

245 Репутация автора

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

select  distinct * into t2 from t1;
delete from t1;
insert into t1 select *  from t2;
drop table t2;
Автор: JuanJo Размещён: 05.02.2013 09:44

13 плюса

1185 Репутация автора

Еще одно простое решение можно найти по ссылке, вставленной здесь . Это легко понять и кажется эффективным для большинства подобных проблем. Это для 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

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

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

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) 
Автор: Nitish Pareek Размещён: 06.08.2013 05:14

38 плюса

1090 Репутация автора

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

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

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

Автор: Syed Mohamed Размещён: 10.09.2013 01:07

10 плюса

2967 Репутация автора

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

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

Автор: İsmail Yavuz Размещён: 18.09.2013 06:36

7 плюса

71 Репутация автора

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );
Автор: Teena Размещён: 03.10.2013 06:18

6 плюса

230 Репутация автора

DELETE 
FROM MyTable
WHERE NOT EXISTS (
              SELECT min(RowID)
              FROM Mytable
              WHERE (SELECT RowID 
                     FROM Mytable
                     GROUP BY Col1, Col2, Col3
                     ))
               );
Автор: Jayron Soares Размещён: 02.01.2014 03:27

14 плюса

710 Репутация автора

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

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

Автор: Ruben Verschueren Размещён: 27.01.2014 12:20

20 плюса

2751 Репутация автора

Я предпочитаю решение подзапроса \ имеющее 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
--)
Автор: James Errico Размещён: 01.03.2014 07:40

42 плюса

4245 Репутация автора

DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 
Автор: Jithin Shaji Размещён: 21.05.2014 07:54

14 плюса

2840 Репутация автора

Используя 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;
Автор: Ostati Размещён: 13.11.2014 04:20

13 плюса

424 Репутация автора

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

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% дубликатов)

Автор: Draško Размещён: 10.12.2014 07:36

6 плюса

490 Репутация автора

Если вы хотите просмотреть строки, которые вы собираетесь удалить, и сохранить контроль над тем, какие из повторяющихся строк оставить. Смотрите 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
Автор: Lauri Lubi Размещён: 01.01.2015 03:32

31 плюса

3193 Репутация автора

Я бы предпочел 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)
Автор: Shamseer K Размещён: 19.05.2015 02:35

11 плюса

3744 Репутация автора

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

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
Автор: Haris Размещён: 23.07.2015 11:42

9 плюса

145 Репутация автора

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

 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

Автор: Harikesh Yadav Размещён: 28.09.2016 05:26

22 плюса

319 Репутация автора

Чтобы получить дубликаты строк:

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);      
Автор: Shaini Sinha Размещён: 29.12.2016 10:31
Вопросы из категории :
32x32