[MySQL] Выбрать дубликаты записей в нескольких таблицах

Есть две таблицы со структурой:

Tables

Таблицы связанны соотношением «один-ко-многим» по полю match_id. Другими словами, одному матчу соответствует несколько слотов. В конкрентом примере известно, что для каждого матча есть 10 слотов.

Необходимо найти те матчи, которые «переигрывались». В «физическом» смысле это значит, что надо найти те матчи, где для каждого из 10 слотов поле hero_id содержит одинаковое значение для каждого матча (тоже самое и для поля account_id). Поля radiant_team_id, dire_team_id — это идентификаторы команд и они так же должны совпадать для матчей-переигровок.

Как не странно, но это можно сделать всего за один запрос к БД:

SELECT m1.match_id, 
       m2.match_id
FROM   matches AS m1 
       JOIN matches AS m2 
         ON m2.radiant_team_id = m1.radiant_team_id 
            AND m2.dire_team_id = m1.dire_team_id 
            AND m1.match_id < m2.match_id 
       JOIN slots AS s2 
         ON s2.match_id = m2.match_id 
       JOIN slots AS s1 
         ON s1.match_id = m1.match_id 
            AND s1.player_slot = s2.player_slot 
            AND s1.hero_id = s2.hero_id 
GROUP  BY m1.match_id, 
          m2.match_id 
HAVING COUNT(*) = 10

В данном коде используется self-join, то есть таблица matches присоединяется сама к себе. Тоже происходит и с таблицей slots. В первом join’e идет соответствие по полям radiant_team_id, dire_team_id. Так же проверяется, чтобы match_id из двух matches (m1, m2) были не просто разными, а именно m1.match_id должен быть меньше чем m2.match_id. Таким образом получится избежать дублирования в результатах выполнения запроса. Тут можно было указать и обратное условние (m1.match_id > m2.match_id) — в таком случае поменялся бы только порядок вывода результатов, но не само множество результатов. А вот если указать m1.match_id <> m2.match_id, то результат запроса будет содержать в два раза больше данных.

Второй join — это просто привязка таблицы slots к запросу.

Третий join — это привязка таблицы slots второй раз, а так же задание соответствий для слотов (касательно полей hero_id и account_id).

GROUP BY по match_id нужен, чтобы убрать дублирование в результирующем множестве. Ведь слотов 10 и для каждого из них будет своя строка в результате (с одинаковыми match_id’s). Нам этого не надо.

HAVING дает возможность задать условие, сколько слотов должно совпасть (в данном примере — 10).

Вот и все. Данный запрос выведет список пар матчей, которые переигрывались. Да, если матч переигрывался не один раз, то запрос это так же найдет.

, , ,

Оставить комментарий

Top ↑ | Main page | Back