Bagaimana untuk mengalih keluar pendua dalam jadual SQL berdasarkan berbilang medan

Question

Saya mempunyai meja permainan dengan penerangan berikut:

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

Tetapi setiap perlawanan mempunyai catatan pendua di suatu tempat dalam jadual kerana setiap perlawanan adalah dalam jadual kedua-dua pasukan. Adakah terdapat pernyataan SQL yang boleh saya gunakan untuk melihat dan mengalih keluar semua pendua berdasarkan medan tarikh, masa, hometeam_id, awayteam_id, locationcity dan locationstate yang sama?

Answer 1

Anda boleh mencuba pertanyaan seperti ini:

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 )

Ini akan menyimpan hanya satu contoh bagi setiap contoh permainan dengan ID terkecil dalam pangkalan data.

Answer 2

Anda sepatutnya dapat melakukan subkueri berkorelasi untuk mengalih keluar data. Cari semua baris pendua dan padam semua kecuali baris dengan id terkecil. Untuk MYSQL, anda perlu menggunakan inner joins (bersamaan dengan fungsi EXISTS), seperti berikut:

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)

Untuk menguji, letak 从游戏中删除游戏替换为从游戏中选择*. Jangan hanya jalankan delete pada pangkalan data :-)