mysql 删除子查询结果_mysql – 使用子查询重复数据删除

我继承了一个缺乏唯一约束的数据库,因此数据是重复的.我现在正在尝试删除重复的记录,然后添加一个约束来阻止这种情况发生.

我有这个问题：

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 ;

另一种方法是使用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

;

标签：mysql,subquery,duplication,functions

来源： https://codeday.me/bug/20190807/1606083.html