Jak mogę usunąć zduplikowane wiersze?

Innym możliwym sposobem na to jest

; 

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

Używam ORDER BY (SELECT 0) powyżej, ponieważ jest dowolne, który wiersz zachować w przypadku remisu.

Aby zachować najnowszą W RowID kolejność na przykład można użyć ORDER BY RowID DESC

Plany Realizacji

Plan wykonania tego zadania jest często prostszy i bardziej efektywny niż w przyjętej odpowiedzi, ponieważ nie wymaga samodzielnego łączenia.

Nie zawsze tak jest jednak. Jednym z miejsc, gdzie rozwiązanie GROUP BY może być preferowane, są sytuacje, w których agregat hashowy zostanie wybrany zamiast agregatu strumieniowego.

Rozwiązanie ROW_NUMBER daje zawsze ten sam plan, podczas gdy strategia GROUP BY jest bardziej elastyczna.

Czynniki, które mogłyby sprzyjać podejściu hash aggregate, to

• brak użytecznego indeksu na kolumnach partycjonowania
• stosunkowo mniej grup z stosunkowo więcej duplikatów w każdej grupie

W skrajnych wersjach tego drugiego przypadku (jeśli jest bardzo mało grup z wieloma duplikatami w każdej) Można również rozważyć po prostu wstawienie wierszy, aby zachować je w nowej tabeli, a następnie TRUNCATE-skopiowanie oryginału i skopiowanie ich z powrotem, aby zminimalizować rejestrowanie w porównaniu do usunięcia bardzo dużej części wierszy.

Jest dobry artykuł na temat usuwania duplikatów w witrynie pomocy technicznej Microsoft. To dość konserwatywne - mają zrobić wszystko w oddzielnych krokach-ale to powinno działać dobrze na dużych stołach.

W przeszłości używałem do tego autouzupełniania, chociaż prawdopodobnie można by to upiększyć klauzulą HAVING:

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

Poniższe zapytanie jest przydatne do usuwania zduplikowanych wierszy. Tabela w tym przykładzie ma ID jako kolumnę tożsamości, a kolumny, które mają zduplikowane dane są Column1, Column2 i 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) 

Poniższy skrypt pokazuje użycie GROUP BY, HAVING, ORDER BY w jednym zapytaniu i zwraca wyniki z zduplikowaną kolumną i jej liczbą.

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

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

Spowoduje to usunięcie zduplikowanych wierszy, z wyjątkiem pierwszego wiersza

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)

Wolałbym CTE do usuwania duplikatów wierszy z tabeli sql server

Zdecydowanie polecam ten artykuł:: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

Zachowując oryginał

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

Bez zachowania oryginału

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)

Szybkie i brudne usuwanie dokładnie zduplikowanych wierszy (dla małych tabel):

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

Preferuję rozwiązanie subquery \ having count (*) > 1 do połączenia wewnętrznego, ponieważ okazało się, że jest łatwiejsze do odczytania i bardzo łatwo było przekształcić ją w instrukcję SELECT, aby zweryfikować, co zostanie usunięte przed uruchomieniem.

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

Aby Pobrać Zduplikowane Wiersze:

SELECT
name, email, COUNT(*)
FROM 
users
GROUP BY
name, email
HAVING COUNT(*) > 1

Aby usunąć zduplikowane wiersze:

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

Jeszcze jedno proste rozwiązanie można znaleźć pod linkiem wklejonym tutaj . Ten łatwy do uchwycenia i wydaje się być skuteczny w przypadku większości podobnych problemów. Jest to jednak dla SQL Server, ale zastosowana koncepcja jest więcej niż akceptowalna.

Oto odpowiednie fragmenty z linkowanej strony:

Rozważmy te dane:

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

Najpierw Wstaw kolumnę tożsamości w tej tabeli, używając następującego kod:

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

Użyj poniższego kodu, aby go rozwiązać:

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

Pomyślałem, że podzielę się moim rozwiązaniem, ponieważ działa w szczególnych okolicznościach. W moim przypadku tabela z duplikowanymi wartościami nie miała klucza obcego (ponieważ wartości były duplikowane z innego db).

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: pracując nad takimi rzeczami, zawsze korzystam z transakcji, która nie tylko zapewnia, że wszystko jest wykonane jako całość, ale także pozwala mi przetestować bez ryzyka czegokolwiek. Ale oczywiście i tak powinieneś wziąć kopię zapasową dla pewności...

To zapytanie pokazało mi bardzo dobre wykonanie:

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

Usunął 1m wierszy w nieco ponad 30 sekund z tabeli 2m (50% duplikatów)

Korzystanie z CTE. Chodzi o to, aby połączyć się z jedną lub więcej kolumnami, które tworzą zduplikowany rekord, a następnie usunąć, co chcesz:

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

Oto kolejny dobry artykuł na temat usuwania duplikatów .

Omawia dlaczego jego trudne: " SQL jest oparty na algebrze relacyjnej, a duplikaty nie mogą występować w algebrze relacyjnej, ponieważ duplikaty nie są dozwolone w zbiorze."

Rozwiązanie tabeli temp i dwa przykłady mysql.

W przyszłości zamierzasz temu zapobiec na poziomie bazy danych lub z perspektywy aplikacji. Sugerowałbym poziom bazy danych, ponieważ baza danych powinna być odpowiedzialny za zachowanie integralności referencji, Programiści po prostu sprawią problemy;)

Jasne. Użyj tabeli temp. Jeśli chcesz pojedyncze, niezbyt wydajne stwierdzenie, że "działa", możesz użyć:

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)

Zasadniczo, dla każdego wiersza w tabeli, pod-select znajduje Górny wiersz wszystkich wierszy, które są dokładnie takie same, jak dany wiersz. Kończy się to listą identyfikatorów wierszy, które reprezentują" oryginalne", nie zduplikowane wiersze.

Miałem tabelę, w której musiałem zachować Nie zduplikowane wiersze. Nie jestem pewien szybkości czy skuteczności.

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

Innym sposobem jest Utworzenie nowej tabeli z tymi samymi polami i z unikalnym indeksem . Następnie Przenieś wszystkie dane ze starej tabeli do nowej tabeli . Automatycznie ignoruj SQL SERVER (istnieje również opcja co zrobić, jeśli będzie duplikat wartości: ignore, interrupt lub sth) duplikaty wartości. Mamy więc tę samą tabelę bez zduplikowanych wierszy. Jeśli nie chcesz unikalnego indeksu, po przeniesieniu danych możesz go upuścić .

Szczególnie dla większych tabel możesz użyć pakietu DTS (SSIS package to import / export data), aby szybko przenieść wszystkie dane do nowej, unikalnie indeksowanej tabeli. Dla 7 milionów wierszy zajmuje to tylko kilka minut.

Użyj tego

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

Za pomocą poniższego zapytania możemy usunąć zduplikowane rekordy w oparciu o pojedynczą kolumnę lub wiele kolumn. poniżej Zapytanie jest usuwane na podstawie dwóch kolumn. nazwa tabeli to: testing i nazwy kolumn 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. Utwórz nową pustą tabelę o tej samej strukturze

2. Wykonaj zapytanie w ten sposób

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

3. Następnie wykonaj to zapytanie

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

Jest to najprostszy sposób na usunięcie zduplikowanego rekordu

 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

Chciałbym wspomnieć o tym podejściu, jak to może być pomocne, i działa na wszystkich serwerach SQL: Dość często jest tylko jeden-dwa duplikaty, a identyfikatory i liczba duplikatów są znane. W tym przypadku:

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

Z poziomu aplikacji (niestety). Zgadzam się, że właściwym sposobem zapobiegania duplikacji jest na poziomie bazy danych poprzez użycie unikalnego indeksu, ale w SQL Server 2005 indeks może wynosić tylko 900 bajtów, a moje pole varchar (2048) to rozwala.

Nie wiem, jak dobrze by to działało, ale myślę, że mógłbyś napisać WYZWALACZ, aby to wyegzekwować, nawet jeśli nie mógłbyś tego zrobić bezpośrednio z indeksem. Coś w stylu:

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

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

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)

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

Inny sposób na to: --

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