Программирование

Получить все строки с дублирующими адресами MySQL за запрос

Используйте подзапрос или JOIN, чтобы вывести все строки с дублирующими адресами в одной инструкции MySQL, без второго запроса в приложении для ускорения работы.

Авторы: НейроАгент

Нужно получить все строки, у которых в колонке address встречается дублирующийся адрес в таблице list. Сейчас я использую:

sql
SELECT address, COUNT(id) AS cnt
FROM list
GROUP BY address
HAVING cnt > 1;

Этот запрос выдаёт только дублирующиеся адреса, но мне хочется увидеть каждую строку, у которой адрес совпадает, например:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

Как написать один запрос MySQL, который вернёт все строки с дублирующимся адресом, без необходимости выполнять второй запрос в коде приложения?

Значения адресов, которые встречаются более одного раза, можно быстро обнаружить с помощью простого GROUP BY. Как только вы определите, какие адреса дублируются, можно соединить этот результат с исходной таблицей (или воспользоваться подзапросом с IN/EXISTS). Всё это помещается в один запрос и возвращает все строки, у которых адрес повторяется.

sql
-- 1. Соединяем список дублирующихся адресов с полной таблицей
SELECT l.*
FROM list AS l
JOIN (
    SELECT address
    FROM list
    GROUP BY address
    HAVING COUNT(*) > 1
) AS dup
ON l.address = dup.address
ORDER BY l.address;

Почему это работает

  1. Внутренний подзапрос

    sql
    SELECT address
FROM list
GROUP BY address
HAVING COUNT(*) > 1

    выдаёт список значений address, которые встречаются более одного раза в таблице list. См. обсуждение на Stack Overflow, где этот шаблон впервые применён для поиска дубликатов.

  2. Внешний запрос
    Внешний SELECT выбирает все столбцы (*) из исходной таблицы для строк, у которых address совпадает с одним из адресов, возвращённых подзапросом. Соединяя по адресу, вы сразу получаете все строки с повторяющимся адресом. Это та же техника, описанная на Stack Overflow под заголовком «выборка всех дубликатов».

Альтернативный однострочный вариант (подзапрос с IN)

sql
SELECT *
FROM list
WHERE address IN (
    SELECT address
    FROM list
    GROUP BY address
    HAVING COUNT(*) > 1
);

Обе формы эквивалентны; вариант с JOIN может работать чуть быстрее, если по адресу есть индекс.

Использование оконных функций (MySQL 8+)

Если вы используете MySQL 8 или новее, можно обойтись без подзапроса:

sql
SELECT *
FROM (
    SELECT l.*,
           COUNT(*) OVER (PARTITION BY address) AS dup_cnt
    FROM list AS l
) AS t
WHERE dup_cnt > 1;

Оконная функция COUNT(*) OVER (PARTITION BY address) подсчитывает, сколько строк имеют одинаковый адрес. Строки с числом больше одного — это те, которые нам нужны.

Советы по производительности

  • Индексуйте столбец address.
    Простой INDEX (address) значительно ускоряет как группировку/агрегацию, так и соединение или сравнение IN. См. документацию MySQL по индексам для подробностей.

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

Итог

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

Авторы
НейроАгент
Автор
Проверено модерацией
НейроОтветы
Модерация
Получить все строки с дублирующими адресами MySQL за запрос