Как удалить дубликаты в таблице SQL на основе нескольких полей

У меня есть таблица игр, которая описывается следующим образом:

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| date          | date        | NO   |     | NULL    |                |
| time          | time        | NO   |     | NULL    |                |
| hometeam_id   | int(11)     | NO   | MUL | NULL    |                |
| awayteam_id   | int(11)     | NO   | MUL | NULL    |                |
| locationcity  | varchar(30) | NO   |     | NULL    |                |
| locationstate | varchar(20) | NO   |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

Но каждая игра имеет дублирующую запись в таблице где-то, потому что каждая игра была в расписании для двух команд. Есть ли инструкция sql, которую я могу использовать для просмотра и удаления всех дубликатов на основе одинаковых полей даты, времени, hometeam_id, awayteam_id, locationcity и locationstate?

Ответ 1

Вы должны иметь возможность выполнить коррелированный подзапрос, чтобы удалить данные. Найдите все строки, дублирующие и удалите все, кроме одного с наименьшим идентификатором. Для MYSQL необходимо использовать внутреннее соединение (функциональный эквивалент EXISTS):

delete games from games inner join 
    (select  min(id) minid, date, time,
             hometeam_id, awayteam_id, locationcity, locationstate
     from games 
     group by date, time, hometeam_id, 
              awayteam_id, locationcity, locationstate
     having count(1) > 1) as duplicates
   on (duplicates.date = games.date
   and duplicates.time = games.time
   and duplicates.hometeam_id = games.hometeam_id
   and duplicates.awayteam_id = games.awayteam_id
   and duplicates.locationcity = games.locationcity
   and duplicates.locationstate = games.locationstate
   and duplicates.minid <> games.id)

Чтобы проверить, замените delete games from games на select * from games. Не просто запускайте удаление в своей БД: -)

Ответ 2

Вы можете попробовать такой запрос:

DELETE FROM table_name AS t1
WHERE EXISTS (
 SELECT 1 FROM table_name AS t2 
 WHERE t2.date = t1.date 
 AND t2.time = t1.time 
 AND t2.hometeam_id = t1.hometeam_id 
 AND t2.awayteam_id = t1.awayteam_id 
 AND t2.locationcity = t1.locationcity 
 AND t2.id > t1.id )

Это оставит в базе данных только один пример каждого игрового экземпляра с наименьшим идентификатором.

Ответ 3

Лучшее, что сработало для меня, это воссоздать таблицу.

CREATE TABLE newtable SELECT * FROM oldtable GROUP BY field1,field2;

Затем вы можете переименовать.

Ответ 4

Чтобы получить список дубликатов, соответствующих двум полям

select t.ID, t.field1, t.field2
from (
  select field1, field2
  from table_name
  group by field1, field2
  having count(*) > 1) x, table_name t
where x.field1 = t.field1 and x.field2 = t.field2
order by t.field1, t.field2

И удалить все дубликаты только

DELETE x 
FROM table_name x
JOIN table_name y
ON y.field1= x.field1
AND y.field2 = x.field2
AND y.id < x.id;

Ответ 5

select orig.id,
       dupl.id
from   games   orig, 
       games   dupl
where  orig.date   =    dupl.date
and    orig.time   =    dupl.time
and    orig.hometeam_id = dupl.hometeam_id
and    orig. awayteam_id = dupl.awayeam_id
and    orig.locationcity = dupl.locationcity
and    orig.locationstate = dupl.locationstate
and    orig.id     <    dupl.id

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

Ответ 6

Пока вы не получаете идентификатор (первичный ключ) таблицы в выбранном запросе, а другие данные точно такие же, вы можете использовать SELECT DISTINCT, чтобы избежать дублирования результатов.

Ответ 7

delete from games 
   where id not in 
   (select max(id)  from games 
    group by date, time, hometeam_id, awayteam_id, locationcity, locationstate 
    );

Обход

select max(id)  id from games 
    group by date, time, hometeam_id, awayteam_id, locationcity, locationstate
into table temp_table;

delete from games where id in (select id from temp);

Ответ 8

DELETE FROM table
WHERE id = 
    (SELECT t.id
    FROM table as t
    JOIN (table as tj ON (t.date = tj.data
                          AND t.hometeam_id = tj.hometeam_id
                          AND t.awayteam_id = tj.awayteam_id
                          ...))