Jak usunąć zduplikowane wiersze w programie SQL Server?

Jak mogę usunąć zduplikowane wiersze, których nie unique row idma?

Mój stolik jest

col1  col2 col3 col4 col5 col6 col7
john  1    1    1    1    1    1 
john  1    1    1    1    1    1
sally 2    2    2    2    2    2
sally 2    2    2    2    2    2

Chcę, aby po usunięciu duplikatu pozostały następujące informacje:

john  1    1    1    1    1    1
sally 2    2    2    2    2    2

Próbowałem kilka zapytań, ale myślę, że zależą one od identyfikatora wiersza, ponieważ nie otrzymuję pożądanego rezultatu. Na przykład:

DELETE
FROM table
WHERE col1 IN (
    SELECT id
    FROM table
    GROUP BY id
    HAVING (COUNT(col1) > 1)
)

I jak współczynniki CTE i ROW_NUMBERjako dwa połączone pozwalają nam zobaczyć, które wiersze są usuwane (lub aktualizowany), więc po prostu zmienić DELETE FROM CTE...na SELECT * FROM CTE:

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

DEMO (wynik jest inny; zakładam, że jest to spowodowane literówką z twojej strony)

COL1    COL2    COL3    COL4    COL5    COL6    COL7
john    1        1       1       1       1       1
sally   2        2       2       2       2       2

Ten przykład określa duplikaty według pojedynczej kolumny z col1powodu PARTITION BY col1. Jeśli chcesz dołączyć wiele kolumn, po prostu dodaj je do PARTITION BY:

ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)

Wolę CTE do usuwania duplikatów wierszy z tabeli serwera SQL

zdecydowanie zalecamy śledzenie tego artykułu :: 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 oryginalności

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)

Bez użycia CTEi ROW_NUMBER()możesz po prostu usunąć rekordy tylko za pomocą grupowania według MAXfunkcji tutaj jest i przykład

DELETE
FROM MyDuplicateTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyDuplicateTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

DELETE from search
where id not in (
   select min(id) from search
   group by url
   having count(*)=1

   union

   SELECT min(id) FROM search
   group by url
   having count(*) > 1
)

Zobacz także poniższy sposób usuwania.

Declare @table table
(col1 varchar(10),col2 int,col3 int, col4 int, col5 int, col6 int, col7 int)
Insert into @table values 
('john',1,1,1,1,1,1),
('john',1,1,1,1,1,1),
('sally',2,2,2,2,2,2),
('sally',2,2,2,2,2,2)

Utworzono przykładową tabelę o nazwie @tablei załadowano ją z podanymi danymi.

Delete  aliasName from (
Select  *,
        ROW_NUMBER() over (Partition by col1,col2,col3,col4,col5,col6,col7 order by col1) as rowNumber
From    @table) aliasName 
Where   rowNumber > 1

Select * from @table

Uwaga: Jeśli podajesz wszystkie kolumny w Partition byczęści, order bynie mają większego znaczenia.

Wiem, że pytanie zostało zadane trzy lata temu, a moja odpowiedź to kolejna wersja tego, co napisał Tim, ale publikowanie tylko dlatego, że jest pomocne dla każdego.

Jeśli nie masz żadnych odniesień, takich jak klucze obce, możesz to zrobić. Często to robię, testując dowody koncepcji, a dane testowe są duplikowane.

SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]

INTO [newTable]

Przejdź do eksploratora obiektów i usuń starą tabelę.

Zmień nazwę nowego stołu na nazwę starego.

Microsoft ma bardzo dobry przewodnik na temat usuwania duplikatów. Sprawdź http://support.microsoft.com/kb/139444

W skrócie, oto najprostszy sposób na usunięcie duplikatów, gdy masz tylko kilka wierszy do usunięcia:

SET rowcount 1;
DELETE FROM t1 WHERE myprimarykey=1;

myprimarykey to identyfikator wiersza.

Ustawiłem wartość rowcount na 1, ponieważ miałem tylko dwa wiersze, które zostały zduplikowane. Gdybym miał zduplikowane 3 wiersze, ustawiłbym wartość rowcount na 2, aby usunąć pierwsze dwa, które widzi, i zostawić tylko jeden w tabeli t1.

Mam nadzieję, że to pomoże każdemu

Spróbuj użyć:

SELECT linkorder
    ,Row_Number() OVER (
        PARTITION BY linkorder ORDER BY linkorder DESC
        ) AS RowNum
FROM u_links

Po wypróbowaniu powyższego sugerowanego rozwiązania działa to w przypadku małych średnich tabel. Mogę zasugerować to rozwiązanie dla bardzo dużych stołów. ponieważ działa w iteracjach.

1. Porzuć wszystkie widoki zależności LargeSourceTable
2. można znaleźć zależności za pomocą narzędzia do zarządzania sql, kliknij prawym przyciskiem myszy tabelę i kliknij „Wyświetl zależności”
3. Zmień nazwę tabeli:
4. sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO
5. Utwórz LargeSourceTableponownie, ale teraz dodaj klucz podstawowy ze wszystkimi kolumnami, które definiują dodawanie duplikatówWITH (IGNORE_DUP_KEY = ON)

6. Na przykład:

   CREATE TABLE [dbo].[LargeSourceTable] ( ID int IDENTITY(1,1), [CreateDate] DATETIME CONSTRAINT [DF_LargeSourceTable_CreateDate] DEFAULT (getdate()) NOT NULL, [Column1] CHAR (36) NOT NULL, [Column2] NVARCHAR (100) NOT NULL, [Column3] CHAR (36) NOT NULL, PRIMARY KEY (Column1, Column2) WITH (IGNORE_DUP_KEY = ON) ); GO

7. Utwórz ponownie widoki upuszczone w pierwszej kolejności dla nowo utworzonej tabeli

8. Teraz uruchom następujący skrypt sql, zobaczysz wyniki w 1 000 000 wierszy na stronę, możesz zmienić numer wiersza na stronę, aby zobaczyć wyniki częściej.

9. Pamiętaj, że włączam IDENTITY_INSERTi wyłączam, ponieważ jedna kolumna zawiera identyfikator automatycznego przyrostu, który również kopiuję

SET IDENTITY_INSERT LargeSourceTable ON DECLARE @PageNumber AS INT, @RowspPage AS INT DECLARE @TotalRows AS INT declare @dt varchar(19) SET @PageNumber = 0 SET @RowspPage = 1000000 select @TotalRows = count (*) from LargeSourceTable_TEMP

While ((@PageNumber - 1) * @RowspPage < @TotalRows )
Begin
    begin transaction tran_inner
        ; with cte as
        (
            SELECT * FROM LargeSourceTable_TEMP ORDER BY ID
            OFFSET ((@PageNumber) * @RowspPage) ROWS
            FETCH NEXT @RowspPage ROWS ONLY
        )

        INSERT INTO LargeSourceTable 
        (
             ID                     
            ,[CreateDate]       
            ,[Column1]   
            ,[Column2] 
            ,[Column3]       
        )       
        select 
             ID                     
            ,[CreateDate]       
            ,[Column1]   
            ,[Column2] 
            ,[Column3]       
        from cte

    commit transaction tran_inner

    PRINT 'Page: ' + convert(varchar(10), @PageNumber)
    PRINT 'Transfered: ' + convert(varchar(20), @PageNumber * @RowspPage)
    PRINT 'Of: ' + convert(varchar(20), @TotalRows)

    SELECT @dt = convert(varchar(19), getdate(), 121)
    RAISERROR('Inserted on: %s', 0, 1, @dt) WITH NOWAIT
    SET @PageNumber = @PageNumber + 1
End

SET IDENTITY_INSERT LargeSourceTable OFF

Istnieją dwa rozwiązania w mysql:

A) Usuń zduplikowane wiersze za pomocą DELETE JOINinstrukcji

DELETE t1 FROM contacts t1
INNER JOIN contacts t2 
WHERE 
    t1.id < t2.id AND 
    t1.email = t2.email;

To zapytanie dwukrotnie odwołuje się do tabeli kontaktów, dlatego używa aliasu tabeli t1i t2.

Dane wyjściowe to:

1 zapytanie OK, dotyczy 4 wierszy (0,10 s)

Jeśli chcesz usunąć zduplikowane wiersze i zachować lowest id, możesz użyć następującej instrukcji:

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 
WHERE
    c1.id > c2.id AND 
    c1.email = c2.email;

B) Usuń duplikaty wierszy za pomocą tabeli pośredniej

Poniżej przedstawiono kroki usuwania zduplikowanych wierszy za pomocą tabeli pośredniej:

    1. Utwórz nową tabelę o strukturze takiej samej jak oryginalna tabela, którą chcesz usunąć zduplikowane wiersze.

    2. Wstaw różne wiersze z oryginalnej tabeli do bezpośredniej tabeli.

    3. Wstaw różne wiersze z oryginalnej tabeli do bezpośredniej tabeli.

Krok 1. Utwórz nową tabelę, której struktura jest taka sama jak oryginalnej tabeli:

CREATE TABLE source_copy LIKE source;

Krok 2. Wstaw różne wiersze z oryginalnej tabeli do nowej tabeli:

INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values

Krok 3. upuść oryginalny stół i zmień nazwę bezpośredniego stołu na oryginalny

DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;

Źródło: http://www.mysqltutorial.org/mysql-delete-duplicate-rows/

-- this query will keep only one instance of a duplicate record.
;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3-- based on what? --can be multiple columns
                                       ORDER BY ( SELECT 0)) RN
         FROM   Mytable)



delete  FROM cte
WHERE  RN > 1

Musisz pogrupować według duplikatów rekordów zgodnie z polami, a następnie zatrzymać jeden z rekordów i usunąć resztę. Na przykład:

DELETE prg.Person WHERE Id IN (
SELECT dublicateRow.Id FROM
(
select MIN(Id) MinId, NationalCode
 from  prg.Person group by NationalCode  having count(NationalCode ) > 1
 ) GroupSelect
 JOIN  prg.Person dublicateRow ON dublicateRow.NationalCode = GroupSelect.NationalCode 
 WHERE dublicateRow.Id <> GroupSelect.MinId)

Usuwanie duplikatów z ogromnej (kilka milionów rekordów) tabeli może zająć dużo czasu. Sugeruję, aby zamiast usuwać wstawiać zbiorczo do tabeli tymczasowej wybranych wierszy.

--REWRITING YOUR CODE(TAKE NOTE OF THE 3RD LINE) WITH CTE AS(SELECT NAME,ROW_NUMBER() 
OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB) SELECT * INTO #unique_records FROM 
CTE WHERE ID =1;

Można to zrobić na wiele sposobów na serwerze SQL. Najprostszym sposobem jest: Wstawienie odrębnych wierszy ze zduplikowanej tabeli wierszy do nowej tabeli tymczasowej. Następnie usuń wszystkie dane z tabeli duplikatów wierszy, a następnie wstaw wszystkie dane z tabeli tymczasowej, która nie ma duplikatów, jak pokazano poniżej.

select distinct * into #tmp From table
   delete from table
   insert into table
   select * from #tmp drop table #tmp

   select * from table

Usuń zduplikowane wiersze za pomocą Common Table Expression (CTE)

With CTE_Duplicates as 
(select id,name , row_number() 
over(partition by id,name order by id,name ) rownumber  from table  ) 
delete from CTE_Duplicates where rownumber!=1

with myCTE
as

(
select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails
)
Delete from myCTE where Duplicate>1

W odniesieniu do https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server

Pomysł usunięcia duplikatu obejmuje

• a) Ochrona tych wierszy, które nie są duplikowane
• b) Zachowaj jeden z wielu wierszy, które razem kwalifikują się jako duplikaty.

Krok po kroku

• 1) Najpierw zidentyfikuj wiersze spełniające definicję duplikatu i wstaw je do tabeli tymczasowej, powiedz #tableAll.
• 2) Wybierz niepisane (pojedyncze wiersze) lub odrębne wiersze do tabeli temp, powiedz #tableUnique.
• 3) Usuń z tabeli źródłowej dołączając do #tableAll, aby usunąć duplikaty.
• 4) Wstaw do tabeli źródłowej wszystkie wiersze z #tableUnique.
• 5) Upuść #tableAll i #tableUnique

Jeśli masz możliwość tymczasowego dodania kolumny do tabeli, było to dla mnie rozwiązanie:

ALTER TABLE dbo.DUPPEDTABLE ADD RowID INT NOT NULL IDENTITY(1,1)

Następnie wykonaj operację USUŃ używając kombinacji MIN i GROUP BY

DELETE b
FROM dbo.DUPPEDTABLE b
WHERE b.RowID NOT IN (
                     SELECT MIN(RowID) AS RowID
                     FROM dbo.DUPPEDTABLE a WITH (NOLOCK)
                     GROUP BY a.ITEM_NUMBER,
                              a.CHARACTERISTIC,
                              a.INTVALUE,
                              a.FLOATVALUE,
                              a.STRINGVALUE
                 );

Sprawdź, czy operacja DELETE została wykonana poprawnie:

SELECT a.ITEM_NUMBER,
    a.CHARACTERISTIC,
    a.INTVALUE,
    a.FLOATVALUE,
    a.STRINGVALUE, COUNT(*)--MIN(RowID) AS RowID
FROM dbo.DUPPEDTABLE a WITH (NOLOCK)
GROUP BY a.ITEM_NUMBER,
    a.CHARACTERISTIC,
    a.INTVALUE,
    a.FLOATVALUE,
    a.STRINGVALUE
ORDER BY COUNT(*) DESC 

Wynik nie powinien mieć wierszy o liczbie większej niż 1. Na koniec usuń kolumnę rowid:

ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID;

Inny sposób usuwania zdublowanych wierszy bez utraty informacji w jednym kroku jest następujący:

delete from dublicated_table t1 (nolock)
join (
    select t2.dublicated_field
    , min(len(t2.field_kept)) as min_field_kept
    from dublicated_table t2 (nolock)
    group by t2.dublicated_field having COUNT(*)>1
) t3 
on t1.dublicated_field=t3.dublicated_field 
    and len(t1.field_kept)=t3.min_field_kept

Och wow, czuję się tak głupio, przygotowując wszystkie te odpowiedzi, są jak odpowiedź ekspertów ze wszystkimi CTE i tabelą temp. Itp.

I wszystko, co zrobiłem, aby to zadziałało, to po prostu agregacja kolumny identyfikatora za pomocą MAX.

DELETE FROM table WHERE col1 IN (
    SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )
)

UWAGA: może być konieczne wielokrotne uruchomienie go, aby usunąć duplikat, ponieważ spowoduje to usunięcie tylko jednego zestawu duplikatów na raz.

DECLARE @TB TABLE(NAME VARCHAR(100));
INSERT INTO @TB VALUES ('Red'),('Red'),('Green'),('Blue'),('White'),('White')
--**Delete by Rank**
;WITH CTE AS(SELECT NAME,DENSE_RANK() OVER (PARTITION BY NAME ORDER BY NEWID()) ID FROM @TB)
DELETE FROM CTE WHERE ID>1
SELECT NAME FROM @TB;
--**Delete by Row Number** 
;WITH CTE AS(SELECT NAME,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB)
DELETE FROM CTE WHERE ID>1;
SELECT NAME FROM @TB;

DELETE FROM TBL1  WHERE ID  IN
(SELECT ID FROM TBL1  a WHERE ID!=
(select MAX(ID) from TBL1  where DUPVAL=a.DUPVAL 
group by DUPVAL
having count(DUPVAL)>1))