Поиск повторяющихся значений в таблице SQL

Легко найти duplicates с одним полем:

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

Поэтому, если у нас есть таблица

 ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com
 

Этот запрос даст нам Джона, Сэма, Тома, Тома, потому что все они имеют одинаковые email .

Однако я хочу получить дубликаты с теми же email и name .

То есть, я хочу получить «Том», «Том».

Причина, в которой я нуждаюсь в этом: я допустил ошибку и позволил вставить повторяющиеся значения name и email . Теперь мне нужно удалить /изменить дубликаты, поэтому мне нужно сначала их найти .

вопрос задан 7.04.2010
Alex
10634 репутация

24 ответов


  • 2206 рейтинг
     SELECT
        name, email, COUNT(*)
    FROM
        users
    GROUP BY
        name, email
    HAVING 
        COUNT(*) > 1
     

    Просто группируйтесь на обоих столбцах.

    Примечание: более старый стандарт ANSI должен иметь все неагрегированные столбцы в GROUP BY, но это изменилось с идеей «функциональной зависимости» :

    В теории реляционных баз данных функциональная зависимость представляет собой ограничение между двумя наборами атрибутов в отношении из базы данных. Другими словами, функциональная зависимость - это ограничение, которое описывает взаимосвязь между атрибутами в отношении.

    Поддержка несовместима:

    ответ дан gbn, с репутацией 332307, 7.04.2010
  • 279 рейтинг

    попробуй это:

     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
     

    ВЫВОД:

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

    если вы хотите, чтобы идентификаторы дубликатов использовали это:

     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
     

    ВЫВОД:

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

    для удаления дубликатов попробуйте:

     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
     

    ВЫВОД:

     id          name       email
    ----------- ---------- --------------
    1           John       John-email
    3           fred       John-email
    4           fred       fred-email
    5           sam        sam-email
    
    (4 row(s) affected)
     
    ответ дан KM., с репутацией 80690, 7.04.2010
  • 90 рейтинг

    Попробуй это:

     SELECT name, email
    FROM users
    GROUP BY name, email
    HAVING ( COUNT(*) > 1 )
     
    ответ дан Chris Van Opstal, с репутацией 27178, 7.04.2010
  • 44 рейтинг

    Если вы хотите удалить дубликаты, это гораздо более простой способ сделать это, чем найти четные /нечетные строки в тройном подвыборке:

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

    И так удалить:

     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
    )
     

    Гораздо легче читать и понимать ИМХО

    Примечание . Единственная проблема заключается в том, что вы должны выполнить запрос до тех пор, пока не удалите строки, поскольку каждый раз удаляйте по одному из каждого дубликата

    ответ дан AncAinu, с репутацией 4139, 14.03.2016
  • 30 рейтинг

    Попробуйте следующее:

     SELECT * FROM
    (
        SELECT Id, Name, Age, Comments, Row_Number() OVER(PARTITION BY Name, Age ORDER By Name)
            AS Rank 
            FROM Customers
    ) AS B WHERE Rank>1
     
    ответ дан gaurav singh, с репутацией 301, 31.12.2013
  • 24 рейтинг
     SELECT name, email 
        FROM users
        WHERE email in
        (SELECT email FROM users
        GROUP BY email 
        HAVING COUNT(*)>1)
    
    ответ дан PRADEEPTA VIRLLEY, с репутацией 259, 22.07.2015
  • 16 рейтинг

    Немного поздно на вечеринку, но я нашел действительно крутое обходное решение для поиска всех дубликатов идентификаторов:

     SELECT GROUP_CONCAT( id )
    FROM users
    GROUP BY email
    HAVING ( COUNT(email) > 1 )
     
    ответ дан Indivision Dev, с репутацией 645, 17.11.2015
  • 15 рейтинг

    попробуйте этот код

     WITH CTE AS
    
    ( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
    FROM ccnmaster )
    select * from CTE 
     
    ответ дан Tanmay Nehete, с репутацией 1331, 13.09.2014
  • 14 рейтинг

    В случае, если вы работаете с Oracle, этот способ был бы предпочтительнее:

     create table my_users(id number, name varchar2(100), email varchar2(100));
    
    insert into my_users values (1, 'John', 'asd@asd.com');
    insert into my_users values (2, 'Sam', 'asd@asd.com');
    insert into my_users values (3, 'Tom', 'asd@asd.com');
    insert into my_users values (4, 'Bob', 'bob@asd.com');
    insert into my_users values (5, 'Tom', 'asd@asd.com');
    
    commit;
    
    select *
      from my_users
     where rowid not in (select min(rowid) from my_users group by name, email);
     
    ответ дан xDBA, с репутацией 231, 16.06.2014
  • 13 рейтинг

    Это выбирает /удаляет все повторяющиеся записи, кроме одной записи из каждой группы дубликатов. Таким образом, удаление удаляет все уникальные записи + одну запись из каждой группы дубликатов.

    Выберите дубликаты:

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

    Удалить дубликаты:

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

    Помните о большем количестве записей, это может вызвать проблемы с производительностью.

    ответ дан Martin Silovský, с репутацией 161, 22.02.2017
  • 8 рейтинг
    select id,name,COUNT(*) from India group by Id,Name having COUNT(*)>1
    
    ответ дан Debendra Dash, с репутацией 1916, 12.09.2016
  • 7 рейтинг

    Как мы можем считать дублированные значения? либо он повторяется 2 раза или больше 2. просто считайте их, а не групповым.

    так просто как

     select COUNT(distinct col_01) from Table_01
     
    ответ дан Muhammad Tahir, с репутацией 112, 11.12.2014
  • 7 рейтинг

    Если вы хотите увидеть, есть ли повторяющиеся строки в вашей таблице, я использовал ниже Query:

     create table my_table(id int, name varchar(100), email varchar(100));
    
    insert into my_table values (1, 'shekh', 'shekh@rms.com');
    insert into my_table values (1, 'shekh', 'shekh@rms.com');
    insert into my_table values (2, 'Aman', 'aman@rms.com');
    insert into my_table values (3, 'Tom', 'tom@rms.com');
    insert into my_table values (4, 'Raj', 'raj@rms.com');
    
    
    Select COUNT(1) As Total_Rows from my_table 
    Select Count(1) As Distinct_Rows from ( Select Distinct * from my_table) abc 
     
    ответ дан shekhar singh, с репутацией 111, 26.08.2014
  • 6 рейтинг

    Это легкая вещь, которую я придумал. Он использует общее табличное выражение (CTE) и окно раздела (я думаю, что эти функции находятся в SQL 2008 и более поздних версиях).

    В этом примере найдены все ученики с двойным именем и добом. Поля, которые вы хотите проверить на дублирование, перечислены в предложении OVER. Вы можете включать любые другие поля, которые вы хотите в проекции.

     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
     
    ответ дан Darrel Lee, с репутацией 1317, 1.07.2016
  • 5 рейтинг
    select name, email
    , case 
    when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
    else 'No'
    end "duplicated ?"
    from users
    
    ответ дан Narendra, с репутацией 69, 8.09.2016
  • 5 рейтинг

    Это также должно работать, возможно, попробуйте.

       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)
     

    Особенно хорошо в вашем случае. Если вы ищете дубликаты, у которых есть префикс или общие изменения, например, новый домен в почте. то вы можете использовать replace () в этих столбцах

    ответ дан veritaS, с репутацией 192, 14.04.2016
  • 5 рейтинг

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

    Я думаю, что это будет работать правильно, чтобы искать повторяющиеся значения в определенном столбце.

    ответ дан user4877838, с репутацией 79, 8.05.2015
  • 4 рейтинг

    Если вы хотите найти дубликаты данных (по одному или нескольким критериям) и выбрать фактические строки.

     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/better-sql-group-by-find-duplicate-data/

    ответ дан Lauri Lubi, с репутацией 450, 1.01.2015
  • 4 рейтинг
     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
    /
    
    ответ дан naveed, с репутацией 61, 15.10.2014
  • 4 рейтинг

    Используя CTE, мы также можем найти дублирующее значение, подобное этому

     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
     
    ответ дан Debendra Dash, с репутацией 1916, 26.09.2016
  • 3 рейтинг
    SELECT * FROM users u where rowid = (select max(rowid) from users u1 where
    u.email=u1.email);
    
    ответ дан Panky031, с репутацией 163, 22.07.2016
  • 1 рейтинг

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

    ответ дан rahul kumar, с репутацией 65, 5.12.2017
  • -1 рейтинг
    SELECT
      FirstName, LastName, MobileNo, COUNT(1) as CNT 
    FROM        
      CUSTOMER
    GROUP BY
      FirstName, LastName, MobileNo 
    HAVING
      COUNT(1) > 1;
    
    ответ дан Anil, с репутацией 39, 7.01.2015
  • -1 рейтинг

    /Как получить повторяющуюся запись в таблице в sql /
    SELECT COUNT (EmpCode), EmpCode FROM tbl_Employees WHERE Status = 1 GROUP BY EmpCode HAVING COUNT (EmpCode)> 1

    ответ дан JIYAUL MUSTAPHA, с репутацией 14, 27.09.2018