Znajdowanie zduplikowanych wartości w tabeli SQL
Łatwo znaleźć duplikaty z jednym polem:
SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1
Więc jeśli mamy tabelę
ID NAME EMAIL
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected]
4 Bob [email protected]
5 Tom [email protected]
To zapytanie da nam John, Sam, Tom, Tom, ponieważ wszystkie mają to samo email
.
Jednak chcę uzyskać duplikaty z tym samym email
oraz name
.
Powód, dla którego tego potrzebuję: popełniłem błąd i pozwoliłem wstawić duplikaty name
i email
wartości. Teraz muszę usunąć / zmienić duplikaty, więc najpierw muszę je znaleźć.
30 answers
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING
COUNT(*) > 1
Po prostu Grupuj na obu kolumnach.
Uwaga: starszy standard ANSI ma mieć wszystkie niezagregowane kolumny w grupie BY, ale to zmieniło się wraz z ideą "zależności funkcyjne":
W teorii relacyjnych baz danych zależność funkcjonalna jest ograniczeniem pomiędzy dwoma zestawami atrybutów w relacji z bazy danych. Innymi słowy, zależność funkcjonalna jest ograniczeniem opisującym zależność między atrybutami w pokrewieństwo.
Wsparcie nie jest spójne:
- Recent PostgreSQL wspiera go .
- SQL Server (jak w SQL Server 2017) nadal wymaga wszystkich niezagregowanych kolumn w grupie BY.
- MySQL jest nieprzewidywalny i potrzebujesz
sql_mode=only_full_group_by
:- Grupuj według kolejności nazw, wyświetlając błędne wyniki ;
- która jest najtańszą funkcją agregacyjną przy braku ANY () (patrz komentarze w zaakceptowanym odpowiedź).
- Wyrocznia nie jest wystarczająco mainstreamowa(Uwaga: humor, Nie wiem jak Wyrocznia).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-01 11:59:42
Spróbuj tego:
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
Wyjście:
name email CountOf
---------- ----------- -----------
John John-email 2
sam sam-email 2
(2 row(s) affected)
Jeśli chcesz identyfikatory dupów użyj tego:
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
Wyjście:
id name email
----------- ---------- ------------
1 John John-email
2 John John-email
5 sam sam-email
6 sam sam-email
(4 row(s) affected)
Aby usunąć duplikaty spróbuj:
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
Wyjście:
id name email
----------- ---------- --------------
1 John John-email
3 fred John-email
4 fred fred-email
5 sam sam-email
(4 row(s) affected)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-10 08:46:10
Spróbuj tego:
SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-04-07 18:20:36
Jeśli chcesz usunąć duplikaty, oto o wiele prostszy sposób, aby to zrobić, niż znalezienie parzystych / nieparzystych wierszy w potrójnym pod-selekcie:
SELECT id, name, email
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
I tak do usunięcia:
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
)
O wiele łatwiejsze do odczytania i zrozumienia IMHO
Uwaga: jedynym problemem jest to, że musisz wykonać żądanie, dopóki nie zostanie usunięte żadne wiersze, ponieważ usuwasz tylko 1 z każdego duplikatu za każdym razem
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-30 12:37:35
Spróbuj:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-01-12 10:58:44
SELECT name, email
FROM users
WHERE email in
(SELECT email FROM users
GROUP BY email
HAVING COUNT(*)>1)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-07-22 07:40:54
Trochę za późno na imprezę, ale znalazłem naprawdę fajne obejście, aby znaleźć wszystkie duplikaty ID:
SELECT GROUP_CONCAT( id )
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-11-17 10:21:14
Wypróbuj ten kod
WITH CTE AS
( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-30 08:50:59
Zaznacza / usuwa wszystkie zduplikowane rekordy z wyjątkiem jednego rekordu z każdej grupy duplikatów. Tak więc usunięcie pozostawia wszystkie unikalne rekordy + jeden rekord z każdej grupy duplikatów.
Wybierz duplikaty:
SELECT *
FROM table
WHERE
id NOT IN (
SELECT MIN(id)
FROM table
GROUP BY column1, column2
);
Usuń duplikaty:
DELETE FROM table
WHERE
id NOT IN (
SELECT MIN(id)
FROM table
GROUP BY column1, column2
);
Należy pamiętać o większej ilości rekordów, może to powodować problemy z wydajnością.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-01-09 13:25:49
W przypadku pracy z Oracle, ten sposób byłby preferowany:
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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-16 08:50:58
select name, email
, case
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-08 06:41:06
Jeśli chcesz sprawdzić, czy w Twojej tabeli znajdują się duplikaty wierszy, użyłem poniższego zapytania:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-08-26 10:07:17
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
/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-15 16:41:26
SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;
Myślę, że będzie to działać poprawnie, aby wyszukać powtarzające się wartości w danej kolumnie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-08 07:10:36
To jest łatwa rzecz, którą wymyśliłem. Używa wspólnego wyrażenia tabeli (CTE) i okna partycji (myślę, że te funkcje są w SQL 2008 i Później).
Ten przykład znajduje wszystkich uczniów z duplikatem imienia i nazwiska. Pola, które chcesz sprawdzić pod kątem duplikacji, znajdują się w klauzuli OVER. W projekcji można umieścić dowolne inne pola.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-01 19:20:46
select id,name,COUNT(*) from user group by Id,Name having COUNT(*)>1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-01-08 18:16:51
Jak możemy policzyć zduplikowane wartości?? albo powtarza się 2 razy lub więcej niż 2. policz je, a nie grupowo.
Tak proste jak
select COUNT(distinct col_01) from Table_01
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-12-11 10:32:06
Używając CTE możemy również znaleźć zduplikowaną wartość w ten sposób
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-26 12:23:30
To też powinno zadziałać, może spróbujemy.
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)
Szczególnie dobre w Twoim przypadku, jeśli szukasz duplikatów, które mają jakiś prefiks lub ogólną zmianę, jak np. nowa domena W mail. następnie możesz użyć replace () w tych kolumnach
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-14 23:02:15
SELECT * FROM users u where rowid = (select max(rowid) from users u1 where
u.email=u1.email);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-22 20:29:29
SELECT name, email,COUNT(email)
FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(email) > 1)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-02-17 19:50:23
Jeśli chcesz znaleźć duplikaty danych (według jednego lub kilku kryteriów) i wybrać rzeczywiste wiersze.
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/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-01 14:43:33
Najważniejszą rzeczą jest tutaj, aby mieć najszybszą funkcję. Należy również zidentyfikować wskaźniki duplikatów. Self join jest dobrą opcją, ale aby mieć szybszą funkcję, lepiej najpierw znaleźć wiersze, które mają duplikaty, a następnie połączyć z oryginalną tabelą, aby znaleźć id zduplikowanych wierszy. Na koniec Uporządkuj według dowolnej kolumny z wyjątkiem id, aby zduplikowane wiersze były blisko siebie.
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-05-28 20:34:44
aby usunąć rekordy, których nazwy są zduplikowane
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS T FROM @YourTable
)
DELETE FROM CTE WHERE T > 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-01-10 15:06:09
Aby sprawdzić z zduplikowanego rekordu w tabeli.
select * from users s
where rowid < any
(select rowid from users k where s.name = k.name and s.email = k.email);
Lub
select * from users s
where rowid not in
(select max(rowid) from users k where s.name = k.name and s.email = k.email);
Aby usunąć zduplikowany rekord w tabeli.
delete from users s
where rowid < any
(select rowid from users k where s.name = k.name and s.email = k.email);
Lub
delete from users s
where rowid not in
(select max(rowid) from users k where s.name = k.name and s.email = k.email);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-03-18 17:32:16
SELECT column_name,COUNT(*) FROM TABLE_NAME GROUP BY column1, HAVING COUNT(*) > 1;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-11 13:15:13
Możemy użyć tutaj, które działają na funkcjach agregujących, jak pokazano poniżej
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
Tutaj jako dwa pola id_account i data są używane z Count (*). Tak więc poda wszystkie rekordy, które mają więcej niż jeden raz takie same wartości w obu kolumnach.
Z jakiegoś powodu przegapiliśmy dodanie jakichkolwiek ograniczeń w tabeli SQL server i rekordy zostały wstawione do wszystkich kolumn z aplikacją front-end. Następnie możemy użyć poniższego zapytania, aby usunąć zduplikowane zapytanie z stolik.
SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable
Tutaj wzięliśmy wszystkie odrębne rekordy oryginalnej tabeli i usunęliśmy rekordy oryginalnej tabeli. Ponownie wstawiliśmy wszystkie różne wartości z nowej tabeli do oryginalnej tabeli, a następnie usunęliśmy nową tabelę.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-10-26 17:18:25
Możesz spróbować tego
SELECT NAME, EMAIL, COUNT(*)
FROM USERS
GROUP BY 1,2
HAVING COUNT(*) > 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-06-25 16:30:23
Inny prosty sposób, który możesz wypróbować również za pomocą funkcji analitycznej:
SELECT * from
(SELECT name, email,
COUNT(name) OVER (PARTITION BY name, email) cnt
FROM users)
WHERE cnt >1;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-11-19 15:56:25
Możesz użyć słowa kluczowego SELECT DISTINCT, aby pozbyć się duplikatów. Możesz również filtrować według nazwy i uzyskać wszystkich o tej nazwie na stole.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-04-04 14:21:18