Как удалить дубликаты в таблице 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?
8 ответов
Вы должны быть в состоянии сделать коррелированный подзапрос, чтобы удалить данные. Найдите все строки, которые являются дубликатами, и удалите все, кроме одной с наименьшим идентификатором. Для 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. Не просто запустите удаление в вашей БД: -)
Вы можете попробовать такой запрос:
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 )
Это оставит в базе данных только один пример каждого экземпляра игры с наименьшим идентификатором.
Лучшее, что сработало для меня, - это воссоздать стол.
CREATE TABLE newtable SELECT * FROM oldtable GROUP BY field1,field2;
Затем вы можете переименовать.
Чтобы получить список повторяющихся записей, соответствующих двум полям
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;
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
это должно дать вам дубликаты; Вы можете использовать его как подзапрос, чтобы указать идентификаторы для удаления.
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);
Если вы не получаете идентификатор (первичный ключ) таблицы в вашем запросе на выборку, а другие данные точно такие же, вы можете использовать SELECT DISTINCT, чтобы избежать дублирования результатов.
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
...))