我错误地在数据库中插入了重复项。我已经建立了一个查询,它返回我想要保留的所有行ID。如何删除查询中未给定的行。你能在回答中包括完整的问题吗。
select min(matches.`id`)from matches
where (stream_match_time, track_match_time, user_id, track_id ) in (
select stream_match_time, track_match_time, user_id, track_id
from matches
group by stream_match_time, track_match_time, user_id, track_id
having count(*) > 1
)
group by stream_match_time, track_match_time, user_id, track_id
您可以使用:
delete from matches where id not in (...)
但是你必须把你的查询嵌套更深一层,如下所示:
delete from matches
where id not in (
select id from (
select min(id) id from matches
where (stream_match_time, track_match_time, juked_user_id, track_id ) in (
select stream_match_time, track_match_time, juked_user_id, track_id
from matches
group by stream_match_time, track_match_time, juked_user_id, track_id
having count(*) > 1
)
group by stream_match_time, track_match_time, juked_user_id, track_id
) t
)
我认为您的查询不需要两个级别的聚合,所以也可以尝试以下操作:
delete from matches
where id not in (
select id from (
select min(id) id
from matches
group by stream_match_time, track_match_time, juked_user_id, track_id
having count(*) > 1
) t
)