sql - Dubbele waarden vinden in een SQL-tabel

Het is gemakkelijk om duplicaten te vinden met één veld:

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

Dus als we een tafel hebben

ID   NAME   EMAIL
1    John   [email protected]
2    Sam    [email protected]
3    Tom    [email protected]
4    Bob    [email protected]
5    Tom    [email protected]

Deze zoekopdracht geeft ons John, Sam, Tom, Tom omdat ze allemaal hetzelfde emailhebben.

Ik wil echter duplicaten krijgen met hetzelfde emailenname.

Dat wil zeggen, ik wil “Tom”, “Tom” krijgen.

De reden waarom ik dit nodig heb: ik heb een fout gemaakt en heb toestemming gegeven voor het invoegen van dubbele waarden voor nameen email. Nu moet ik de duplicaten verwijderen/wijzigen, dus ik moet ze eerst vinden.

Antwoord 1, autoriteit 100%

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

Gewoon groeperen op beide kolommen.

Opmerking: de oudere ANSI-standaard is om alle niet-geaggregeerde kolommen in de GROUP BY te hebben, maar dit is veranderd met het idee van “functionele afhankelijkheid”:

In de relationele databasetheorie is een functionele afhankelijkheid een beperking tussen twee sets attributen in een relatie uit een database. Met andere woorden, functionele afhankelijkheid is een beperking die de relatie tussen attributen in een relatie beschrijft.

Ondersteuning is niet consistent:

Recente PostgreSQL ondersteunt het.
SQL Server (zoals bij SQL Server 2017) vereist nog steeds alle niet-geaggregeerde kolommen in de GROUP BY.
MySQL is onvoorspelbaar en je hebt sql_mode=only_full_group_bynodig:
- GROUP BY lname ORDER BY met verkeerde resultaten;
- Welke is de minst dure aggregaatfunctie bij afwezigheid van ANY()(zie opmerkingen in geaccepteerd antwoord).
Oracle is niet mainstream genoeg (waarschuwing: humor, ik weet niets van Oracle).

Antwoord 2, autoriteit 12%

probeer dit:

declare @YourTable table (id int, name varchar(10), email varchar(50))
INSERT @YourTable VALUES (1,'John','John-email')
INSERT @YourTable VALUES (2,'John','John-email')
INSERT @YourTable VALUES (3,'fred','John-email')
INSERT @YourTable VALUES (4,'fred','fred-email')
INSERT @YourTable VALUES (5,'sam','sam-email')
INSERT @YourTable VALUES (6,'sam','sam-email')
SELECT
    name,email, COUNT(*) AS CountOf
    FROM @YourTable
    GROUP BY name,email
    HAVING COUNT(*)>1

UITGANG:

name       email       CountOf
---------- ----------- -----------
John       John-email  2
sam        sam-email   2
(2 row(s) affected)

als je de ID’s van de dups wilt, gebruik dan dit:

SELECT
    y.id,y.name,y.email
    FROM @YourTable y
        INNER JOIN (SELECT
                        name,email, COUNT(*) AS CountOf
                        FROM @YourTable
                        GROUP BY name,email
                        HAVING COUNT(*)>1
                    ) dt ON y.name=dt.name AND y.email=dt.email

Uitgang:

id          name       email
----------- ---------- ------------
1           John       John-email
2           John       John-email
5           sam        sam-email
6           sam        sam-email
(4 row(s) affected)

Om de duplicaten te verwijderen, probeer dan:

DELETE d
    FROM @YourTable d
        INNER JOIN (SELECT
                        y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
                        FROM @YourTable y
                            INNER JOIN (SELECT
                                            name,email, COUNT(*) AS CountOf
                                            FROM @YourTable
                                            GROUP BY name,email
                                            HAVING COUNT(*)>1
                                        ) dt ON y.name=dt.name AND y.email=dt.email
                   ) dt2 ON d.id=dt2.id
        WHERE dt2.RowRank!=1
SELECT * FROM @YourTable

Uitgang:

id          name       email
----------- ---------- --------------
1           John       John-email
3           fred       John-email
4           fred       fred-email
5           sam        sam-email
(4 row(s) affected)

Antwoord 3, Autoriteit 4%

Probeer dit:

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

Antwoord 4, autoriteit 2%

Als u de duplicaten wilt verwijderen, is hier een veel eenvoudigere manier om dat te doen dan even/oneven rijen te zoeken in een drievoudige subselectie:

SELECT id, name, email 
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id

En zo te verwijderen:

DELETE FROM users
WHERE id IN (
    SELECT id/*, name, email*/
    FROM users u, users u2
    WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)

Veel gemakkelijker te lezen en te begrijpen IMHO

Opmerking:het enige probleem is dat u het verzoek moet uitvoeren totdat er geen rijen zijn verwijderd, aangezien u elke keer slechts 1 van elk duplicaat verwijdert

Antwoord 5, autoriteit 2%

In tegenstelling tot andere antwoorden kunt u de helerecords met alle kolommen bekijken als die er zijn. Kies in het PARTITION BY-gedeelte van row_numberde gewenste unieke /dubbele kolommen.

SELECT  *
FROM    (
 SELECT a.*
 ,      Row_Number() OVER (PARTITION BY Name, Age ORDER BY Name) AS r
 FROM   Customers AS a
)       AS b
WHERE   r > 1;

Als u ALLEdubbele records met ALLEvelden wilt selecteren, kunt u dit schrijven als

CREATE TABLE test (
        id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
,       c1      integer
,       c2      text
,       d       date DEFAULT now()
,       v       text
);
INSERT INTO test (c1, c2, v) VALUES
(1, 'a', 'Select'),
(1, 'a', 'ALL'),
(1, 'a', 'multiple'),
(1, 'a', 'records'),
(2, 'b', 'in columns'),
(2, 'b', 'c1 and c2'),
(3, 'c', '.');
SELECT * FROM test ORDER BY 1;
SELECT  *
FROM    test
WHERE   (c1, c2) IN (
 SELECT c1, c2
 FROM   test
 GROUP  BY 1,2
 HAVING count(*) > 1
)
ORDER   BY 1;

getest in postgresql .

Antwoord 6

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

Antwoord 7

een beetje laat op het feest, maar ik vond een echt coole oplossing om alle dubbele ID’s te vinden:

SELECT email, GROUP_CONCAT(id)
FROM   users
GROUP  BY email
HAVING COUNT(email) > 1;

Antwoord 8

Hierdoor selecteert / verwijdert alle dubbele records behalve één record van elke groep duplicaten. Dus de Delete verlaat alle unieke records + één record van elke groep van de duplicaten.

Selecteer Duplicaten:

SELECT *
FROM table
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY column1, column2
);

Duplicaten verwijderen:

DELETE FROM table
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY column1, column2
);

Houd er rekening mee dat grotere hoeveelheden records, het kan prestatieproblemen veroorzaken.

Antwoord 9

Probeer deze code

WITH CTE AS
( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE

Antwoord 10

Als u met Oracle werkt, zou op deze manier de voorkeur hebben:

create table my_users(id number, name varchar2(100), email varchar2(100));
insert into my_users values (1, 'John', '[email protected]');
insert into my_users values (2, 'Sam', '[email protected]');
insert into my_users values (3, 'Tom', '[email protected]');
insert into my_users values (4, 'Bob', '[email protected]');
insert into my_users values (5, 'Tom', '[email protected]');
commit;
select *
  from my_users
 where rowid not in (select min(rowid) from my_users group by name, email);

Antwoord 11

select name, email
, case 
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users

Antwoord 12

Als u wilt zien of er dubbele rijen in uw tabel is, gebruikte ik onderstaandequery:

create table my_table(id int, name varchar(100), email varchar(100));
insert into my_table values (1, 'shekh', '[email protected]');
insert into my_table values (1, 'shekh', '[email protected]');
insert into my_table values (2, 'Aman', '[email protected]');
insert into my_table values (3, 'Tom', '[email protected]');
insert into my_table values (4, 'Raj', '[email protected]');
Select COUNT(1) As Total_Rows from my_table 
Select Count(1) As Distinct_Rows from ( Select Distinct * from my_table) abc

Antwoord 13

SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;

Ik denk dat dit goed werkt om herhaalde waarden in een bepaalde kolom te zoeken.

Antwoord 14

select id,name,COUNT(*) from user group by Id,Name having COUNT(*)>1

Antwoord 15

select emp.ename, emp.empno, dept.loc 
          from emp
 inner join dept 
          on dept.deptno=emp.deptno
 inner join
    (select ename, count(*) from
    emp
    group by ename, deptno
    having count(*) > 1)
 t on emp.ename=t.ename order by emp.ename
/

Antwoord 16

Dit is het makkelijke wat ik heb bedacht. Het gebruikt een gemeenschappelijke tabelexpressie (CTE) en een partitievenster (ik denk dat deze functies in SQL 2008 en later zijn).

Dit voorbeeld vindt alle leerlingen met dubbele naam en geboortedatum. De velden die u op duplicatie wilt controleren, komen in de OVER-clausule. U kunt alle andere gewenste velden in de projectie opnemen.

with cte (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName

Antwoord 17

Hoe kunnen we de dubbele waarden tellen??
ofwel wordt het 2 keer herhaald of meer dan 2.
tel ze gewoon, niet groepsgewijs.

zo simpel als

select COUNT(distinct col_01) from Table_01

Antwoord 18

Door CTE te gebruiken, kunnen we ook dubbele waarde vinden

with MyCTE
as
(
select Name,EmailId,ROW_NUMBER() over(PARTITION BY EmailId order by id) as Duplicate from [Employees]
)
select * from MyCTE where Duplicate>1

Antwoord 19

Dit zou ook moeten werken, misschien geef het proberen.

 Select * from Users a
            where EXISTS (Select * from Users b 
                where (     a.name = b.name 
                        OR  a.email = b.email)
                     and a.ID != b.id)

Vooral goed in uw geval als u zoekt naar duplicaten die een soort voorvoegsel of algemene verandering zoals b.v. Nieuw domein in post. Dan kunt u vervangen () op deze kolommen

gebruiken

Antwoord 20

SELECT * FROM users u where rowid = (select max(rowid) from users u1 where
u.email=u1.email);

Antwoord 21

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

Antwoord 22

Het belangrijkste hier is om de snelste functie te hebben. Ook indices van duplicaten moeten worden geïdentificeerd. Zelfmedewerker is een goede optie, maar om een snellere functie te hebben, is het beter om eerst rijen te vinden die duplicaten hebben en vervolgens bij de oorspronkelijke tafel worden gezet voor het vinden van ID van gedupliceerde rijen. Eindelijk bestellen met een kolom behalve id om gedupliceerde rijen in de buurt van elkaar te hebben.

SELECT u.*
FROM users AS u
JOIN (SELECT username, email
      FROM users
      GROUP BY username, email
      HAVING COUNT(*)>1) AS w
ON u.username=w.username AND u.email=w.email
ORDER BY u.email;

Antwoord 23

Als u dubbele gegevens (door een of meerdere criteria) wilt vinden en de werkelijke rijen wilt selecteren.

with MYCTE as (
    SELECT DuplicateKey1
        ,DuplicateKey2 --optional
        ,count(*) X
    FROM MyTable
    group by DuplicateKey1, DuplicateKey2
    having count(*) > 1
) 
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
    AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt

http: //developer.azurewebsites .NET / 2014/09 / BETER-SQL-GROUP-BY-FIND-DUPLICATE-DATA /

Antwoord 24

Om records te verwijderen waarvan de namen dupliceren

;WITH CTE AS    
(
    SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS T FROM     @YourTable    
)
DELETE FROM CTE WHERE T > 1

Antwoord 25

Controleren van dubbele record in een tabel.

select * from users s 
where rowid < any 
(select rowid from users k where s.name = k.name and s.email = k.email);

select * from users s 
where rowid not in 
(select max(rowid) from users k where s.name = k.name and s.email = k.email);

om het dubbele record in een tabel te verwijderen.

delete from users s 
where rowid < any 
(select rowid from users k where s.name = k.name and s.email = k.email);

delete from users s 
where rowid not in 
(select max(rowid) from users k where s.name = k.name and s.email = k.email);

Antwoord 26

Een andere makkelijke manier kunt u deze met behulp van analytische functie proberen zo goed:

SELECT * from 
(SELECT name, email,
COUNT(name) OVER (PARTITION BY name, email) cnt 
FROM users)
WHERE cnt >1;

Antwoord 27

SELECT column_name,COUNT(*) FROM TABLE_NAME GROUP BY column1, HAVING COUNT(*) > 1;

Antwoord 28

U kunt proberen dit

SELECT NAME, EMAIL, COUNT(*)
FROM USERS
GROUP BY 1,2
HAVING COUNT(*) > 1

Antwoord 29

Probeer

SELECT UserID, COUNT(UserID) 
FROM dbo.User
GROUP BY UserID
HAVING COUNT(UserID) > 1

ANTWOORD 30

We kunnen gebruik maken van het hebben van hier, wat werk aan statistische functies zoals hieronder getoond

create table #TableB (id_account int, data int, [date] date)
insert into #TableB values (1 ,-50, '10/20/2018'),
(1, 20, '10/09/2018'),
(2 ,-900, '10/01/2018'),
(1 ,20, '09/25/2018'),
(1 ,-100, '08/01/2018')  
SELECT id_account , data, COUNT(*)
FROM #TableB
GROUP BY id_account , data
HAVING COUNT(id_account) > 1
drop table #TableB

Ook hier twee velden id_account en gegevens worden gebruikt met Count (*). Dus, zal het alle records die meer dan één keer dezelfde waarden in beide kolommen heeft te geven.

We hebben om de een of andere reden per abuis gemist om beperkingen toe te voegen aan de SQL-servertabel en de records zijn duplicaat in alle kolommen ingevoegd met front-end-applicatie. Dan kunnen we onderstaande zoekopdracht gebruiken om dubbele zoekopdrachten uit de tabel te verwijderen.

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

Hier hebben we alle afzonderlijke records van de oorspronkelijke tabel genomen en de records van de oorspronkelijke tabel verwijderd. Opnieuw hebben we alle afzonderlijke waarden van de nieuwe tabel in de originele tabel ingevoegd en vervolgens de nieuwe tabel verwijderd.

Dubbele waarden vinden in een SQL-tabel