我继承了一个缺乏唯一约束的数据库,因此数据是重复的.我现在正在尝试删除重复的记录,然后添加一个约束来阻止这种情况发生.
我有这个问题:
SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1))
FROM `game`
group by matchid, ordinal
having dacount > 1
order by dacount desc
这正确地给了我需要删除的行的ID.但问题是我不能在DELETE上使用它作为子查询,因为带有having参数的dacount.还有另一种方法吗?
这是我的计划:
DELETE FROM game WHERE id IN (SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1))
FROM `game`
GROUP BY matchid, ordinal
HAVING dacount > 1)
解决方法:
MySQL的UPDATE和DELETE实现中存在关于自联接的限制.通常使用连接可以解决该问题:
DELETE g.*
FROM game AS g
JOIN
( SELECT matchid, ordinal, min(id) AS id
FROM game
GROUP BY matchid, ordinal
-- HAVING count(*) > 1
) AS d
ON g.matchid = d.matchid
AND g.ordinal = d.ordinal
AND g.id > d.id ;
在dbfiddle.uk测试.
另一种方法是使用LEFT JOIN / IS NULL结构:
DELETE g.*
FROM game AS g
LEFT JOIN
( SELECT min(id) AS id
FROM game
GROUP BY matchid, ordinal
) AS d
ON g.id = d.id
WHERE d.id IS NULL ;
或者将EXISTS子查询重写为JOIN
(删除存在具有相同matchid,序数和较小id的另一个游戏的所有游戏):
DELETE g.*
FROM game AS g
JOIN
game AS d
ON g.matchid = d.matchid
AND g.ordinal = d.ordinal
AND g.id > d.id
;