Как найти дубликаты строк в таблице SQL?
Подозреваю, что в таблице пользователей один и тот же email завёлся несколько раз. Как найти дубликаты в SQL — то есть значения, которые встречаются больше одного раза? И как потом понять, сколько именно повторов?
2 ответа
Классический приём — сгруппировать по нужному столбцу и оставить группы, где строк больше одной, через HAVING COUNT(*) > 1:
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
В результате — только повторяющиеся email и сколько раз каждый встретился. Если cnt = 3, значит этот email завёлся трижды.
Дубликат может определяться несколькими столбцами — тогда группируйте по всем:
SELECT first_name, last_name, birth_date, COUNT(*)
FROM clients
GROUP BY first_name, last_name, birth_date
HAVING COUNT(*) > 1;
Если нужны сами строки-дубликаты целиком (со всеми полями), а не только значение и счётчик, используют оконную функцию:
SELECT * FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY email) AS cnt
FROM users
) t
WHERE cnt > 1;
Частая ошибка — пытаться фильтровать через WHERE COUNT(*) > 1. Так нельзя: COUNT — агрегат, а WHERE работает до группировки. Условие на счётчик группы всегда идёт в HAVING.
Если задача не просто найти, а удалить дубликаты, оставив по одной строке, удобна оконная функция ROW_NUMBER():
DELETE FROM users
WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER (
PARTITION BY email ORDER BY id) AS rn
FROM users
) t
WHERE rn > 1
);
Она нумерует строки внутри каждой группы email, и мы удаляем все, кроме первой (rn = 1). Перед таким DELETE обязательно сделайте бэкап или прогоните как SELECT.