Hoe kan ik dubbele rijen verwijderenwaar geen unique row idbestaat?
Mijn tafel is
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
Ik wil het volgende overhouden na de dubbele verwijdering:
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
Ik heb een paar query’s geprobeerd, maar ik denk dat ze afhankelijk zijn van een rij-ID, omdat ik niet het gewenste resultaat krijg. Bijvoorbeeld:
DELETE
FROM table
WHERE col1 IN (
SELECT id
FROM table
GROUP BY id
HAVING (COUNT(col1) > 1)
)
Antwoord 1, autoriteit 100%
Ik hou van CTE’s en ROW_NUMBERomdat de twee gecombineerd ons in staat stellen om te zien welke rijen zijn verwijderd (of bijgewerkt), dus verander gewoon de DELETE FROM CTE...in 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(resultaat is anders; ik neem aan dat het te wijten is aan een typefout van uw kant)
COL1 COL2 COL3 COL4 COL5 COL6 COL7
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
Dit voorbeeld bepaalt duplicaten met een enkele kolom col1vanwege de PARTITION BY col1. Als u meerdere kolommen wilt opnemen, voegt u ze gewoon toe aan de Partition by:
ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
Antwoord 2, autoriteit 21%
Ik heb liever CTE voor het verwijderen van dubbele rijen uit de sql-servertabel
raad ten zeerste aan om dit artikel te volgen ::http ://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/
door origineel te houden
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
zonder origineel te houden
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)
Antwoord 3, autoriteit 8%
Zonder CTEen ROW_NUMBER()te gebruiken, kunt u de records gewoon verwijderen door group by te gebruiken met de functie MAXhier is en voorbeeld
DELETE
FROM MyDuplicateTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyDuplicateTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
Antwoord 4, autoriteit 2%
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
)
Antwoord 5, autoriteit 2%
Als u geen referenties heeft, zoals externe sleutels, kunt u dit doen. Ik doe het veel bij het testen van proofs of concept en de testgegevens worden gedupliceerd.
SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]
INTO [newTable]
FROM [oldTable]
Ga naar de objectverkenner en verwijder de oude tabel.
Hernoem de nieuwe tafel met de naam van de oude tafel.
Antwoord 6
Zie ook de onderstaande manier van verwijderen.
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)
Een voorbeeldtabel gemaakt met de naam @tableen deze geladen met de gegeven gegevens.
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
Opmerking: als u alle kolommen in het gedeelte Partition bygeeft, heeft order byniet veel betekenis.
Ik weet het, de vraag is drie jaar geleden gesteld, en mijn antwoord is een andere versie van wat Tim heeft gepost, maar voor het geval dat iemand er iets aan heeft.
Antwoord 7
Er zijn twee oplossingen in mysql:
A)Verwijder dubbele rijen met de instructie DELETE JOIN
DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
t1.id < t2.id AND
t1.email = t2.email;
Deze query verwijst twee keer naar de tabel met contactpersonen en gebruikt daarom de tabelalias t1en t2.
De uitvoer is:
1
Query OK, 4 rijen aangetast (0,10 sec)
Als u dubbele rijen wilt verwijderen en de lowest idwilt behouden, kunt u het volgende statement gebruiken:
DELETE c1 FROM contacts c1
INNER JOIN contacts c2
WHERE
c1.id > c2.id AND
c1.email = c2.email;
B)Dubbele rijen verwijderen met een tussentabel
Het volgende toont de stappen voor het verwijderen van dubbele rijen met behulp van een tussenliggende tabel:
1. Maak een nieuwe tabel met dezelfde structuur als de originele tabel waarvan u dubbele rijen wilt verwijderen.
2. Voeg verschillende rijen in van de originele tabel naar de directe tabel.
3. Voeg verschillende rijen in van de originele tabel naar de directe tabel.
Stap 1. Maak een nieuwe tabel waarvan de structuur hetzelfde is als de originele tabel:
CREATE TABLE source_copy LIKE source;
Stap 2. Voeg verschillende rijen van de oorspronkelijke tabel in de nieuwe tabel in:
INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values
Stap 3. laat de originele tabel vallen en hernoem de directe tabel naar de originele
DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
Bron: http://www.mysqltutorial.org/mysql-delete-duplicate -rijen/
Antwoord 8
Microsoft heeft een erg handige handleiding over het verwijderen van duplicaten. Bekijk http://support.microsoft.com/kb/139444
Kortom, dit is de gemakkelijkste manier om duplicaten te verwijderen als u maar een paar rijen hoeft te verwijderen:
SET rowcount 1;
DELETE FROM t1 WHERE myprimarykey=1;
myprimarykeyis de identifier voor de rij.
Ik heb rijaantalingesteld op 1 omdat ik maar twee rijen had die gedupliceerd waren. Als ik 3 rijen had gedupliceerd, had ik rowcountop 2 gezet, zodat de eerste twee die hij ziet worden verwijderd en er slechts één overblijft in tabel t1.
Ik hoop dat het iemand helpt
Antwoord 9
Probeer te gebruiken:
SELECT linkorder
,Row_Number() OVER (
PARTITION BY linkorder ORDER BY linkorder DESC
) AS RowNum
FROM u_links
Antwoord 10
Het kan op vele manieren in de sql-server
de eenvoudigste manier om dit te doen is:
Voeg de afzonderlijke rijen van de dubbele rijentabel in de nieuwe tijdelijke tabel in. Verwijder vervolgens alle gegevens uit de dubbele rijentabel en voeg vervolgens alle gegevens in uit de tijdelijke tabel die geen duplicaten heeft, zoals hieronder weergegeven.
select distinct * into #tmp From table
delete from table
insert into table
select * from #tmp drop table #tmp
select * from table
Verwijder dubbele rijen met 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
Antwoord 11
Na de voorgestelde oplossing hierboven te hebben geprobeerd, werkt dat voor kleine middelgrote tabellen.
Ik kan die oplossing voorstellen voor zeer grote tafels. omdat het in iteraties loopt.
- Laat alle afhankelijkheidsweergaven van de
LargeSourceTable - u kunt de afhankelijkheden vinden door sql management studio te gebruiken, klik met de rechtermuisknop op de tabel en klik op “Bekijk afhankelijkheden”
- Hernoem de tabel:
sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO- Maak de
LargeSourceTableopnieuw, maar voeg nu een primaire sleutel toe met alle kolommen die de duplicaties definiëren, voegWITH (IGNORE_DUP_KEY = ON) -
Bijvoorbeeld:
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 -
Maak opnieuw de weergaven die u in de eerste plaats hebt laten vallen voor de nieuw gemaakte tabel
-
Voer nu het volgende sql-script uit, u ziet de resultaten in 1.000.000 rijen per pagina, u kunt het rijnummer per pagina wijzigen om de resultaten vaker te zien.
-
Merk op dat ik de
IDENTITY_INSERTaan en uit heb gezet omdat een van de kolommen een automatische incrementele id bevat, die ik ook kopieer
vallen
toe
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
Antwoord 12
Verwijder alle duplicaten, maar de allereerste (met min ID)
zou net zo moeten werken als in andere SQL-servers, zoals Postgres:
DELETE FROM table
WHERE id NOT IN (
select min(id) from table
group by col1, col2, col3, col4, col5, col6, col7
)
Antwoord 13
-- 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
Antwoord 14
U moet op de dubbele records groeperen volgens de veld(en), dan een van de records vasthouden en de rest verwijderen.
Bijvoorbeeld:
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)
Antwoord 15
Het verwijderen van duplicaten uit een enorme tabel (enkele miljoenen records) kan lang duren. Ik stel voor dat u een bulkinvoeging doet in een tijdelijke tabel van de geselecteerde rijen in plaats van te verwijderen.
--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;
Antwoord 16
with myCTE
as
(
select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails
)
Delete from myCTE where Duplicate>1
Antwoord 17
Het idee om duplicaat te verwijderen omvat
- a) Die rijen beschermen die niet dubbel zijn
- b) Bewaar een van de vele rijen die samen kwalificeerden als duplicaat.
Stap voor stap
- 1) Identificeer eerst de rijen die voldoen aan de definitie van duplicaat
en plaats ze in de tijdelijke tabel, zeg #tableAll . - 2) Selecteer niet-duplicaat (enkele rijen) of verschillende rijen in de tijdelijke tabel
zeg #tableUnique. - 3) Verwijderen uit brontabel en voeg #tableAll toe om de . te verwijderen
duplicaten. - 4) Voeg in de brontabel alle rijen van #tableUnique in.
- 5) Zet #tableAll en #tableUnique neer
Antwoord 18
Als je de mogelijkheid hebt om tijdelijk een kolom aan de tabel toe te voegen, was dit een oplossing die voor mij werkte:
ALTER TABLE dbo.DUPPEDTABLE ADD RowID INT NOT NULL IDENTITY(1,1)
Voer vervolgens een verwijdering uit met behulp van een combinatie van min en groep met
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
);
Controleer of de verwijdering correct is uitgevoerd:
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
Het resultaat zou geen rijen moeten hebben met een telling groter dan 1. Verwijder tot slot de RowID-kolom:
ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID;
Antwoord 19
Oh wauw, ik voel me zo stom door klaar al deze antwoorden, ze zijn als experts antwoord met alle CTE en temp tafel en enz.
En alles wat ik deed om het te laten werken was gewoon de ID-kolom geaggregeerd met Max.
DELETE FROM table WHERE col1 IN (
SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )
)
Opmerking: misschien moet u het meerdere tijd uitvoeren om het dubbele te verwijderen, omdat dit slechts één set dubbele rijen tegelijk verwijdert.
Antwoord 20
Dit kan in uw zaak helpen
DELETE t1 FROM table t1 INNER JOIN table t2 WHERE t1.id > t2.id AND t1.col1 = t2.col1
Antwoord 21
Een andere manier om durkische rijen te verwijderen zonder informatie in één stap te verliezen, is als volgt:
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
Antwoord 22
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;
Antwoord 23
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))