我有一張游戲桌，其描述如下：
+---------------+-------------+------+-----+---------+----------------+ | 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 | | +---------------+-------------+------+-----+---------+----------------+
但是每場(chǎng)比賽在表中的某處都有重復(fù)的條目，因?yàn)槊繄?chǎng)比賽都在兩支球隊(duì)的賽程表中。是否有一個(gè) SQL 語(yǔ)句可以用來(lái)根據(jù)相同的日期、時(shí)間、hometeam_id、awayteam_id、locationcity 和 locationstate 字段查看并刪除所有重復(fù)項(xiàng)？
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 )
這將在數(shù)據(jù)庫(kù)中僅保留每個(gè)具有最小 ID 的游戲?qū)嵗囊粋€(gè)示例。
您應(yīng)該能夠執(zhí)行相關(guān)子查詢來(lái)刪除數(shù)據(jù)。查找所有重復(fù)的行并刪除除 id 最小的行之外的所有行。對(duì)于 MYSQL，需要使用內(nèi)連接（相當(dāng)于 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)
要進(jìn)行測(cè)試，請(qǐng)將從游戲中刪除游戲替換為從游戲中選擇*。不要只是在數(shù)據(jù)庫(kù)上運(yùn)行刪除:-)
