So entfernen Sie Duplikate in einer SQL-Tabelle basierend auf mehreren Feldern

Question

Ich habe einen Spieltisch mit der folgenden Beschreibung:

+---------------+-------------+------+-----+---------+----------------+
| 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    |                |
+---------------+-------------+------+-----+---------+----------------+

Aber jedes Spiel hat irgendwo in der Tabelle einen doppelten Eintrag, weil jedes Spiel im Spielplan beider Teams steht. Gibt es eine SQL-Anweisung, mit der ich alle Duplikate basierend auf denselben Feldern für Datum, Uhrzeit, Heimteam-ID, Auswärtsteam-ID, Standortstadt und Standortstatus anzeigen und entfernen kann?

Answer 1

您可以尝试这样的查询：

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 )

这将在数据库中仅保留每个具有最小 ID 的游戏实例的一个示例。

Answer 2

您应该能够执行相关子查询来删除数据。查找所有重复的行并删除除 id 最小的行之外的所有行。对于 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)

要进行测试，请将从游戏中删除游戏替换为从游戏中选择*。不要只是在数据库上运行删除:-)