我有一張遊戲桌，描述如下：
+---------------+-------------+------+-----+---------+----------------+ | 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 語句可以用來根據(jù)相同的日期、時間、hometeam_id、awayteam_id、locationcity 和 locationstate 欄位查看並刪除所有重複？
2
0
0
您可以嘗試這樣的查詢：
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 的遊戲?qū)嵗囊粋€範例。
您應(yīng)該能夠執(zhí)行相關(guān)子查詢來刪除資料。尋找所有重複的行並刪除除 id 最小的行之外的所有行。對於 MYSQL，需要使用內(nèi)部連接（相當於 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)
要進行測試，請將從遊戲中刪除遊戲替換為從遊戲中選擇*。不要只是在資料庫上執(zhí)行刪除:-)
