英文:
Delete duplicated rows MySQL
问题
我们想要在我们的MySQL数据库上删除重复的行，我们尝试了很多查询，但不幸的是我们还没有成功。我们在几篇帖子中找到了这个查询，但也没有成功：
DELETE t1 FROM Raw_Validated_backup AS t1 INNER JOIN Raw_Validated_backup AS t2
ON t1.time_start=t2.time_start
AND t1.time_end=t2.time_end
AND t1.first_temp_lpn=t2.first_temp_lpn
AND t1.first_WL=t2.first_WL
AND t1.first_temp_lpn_validated=t2.first_temp_lpn_validated
AND t1.second_temp_lpn=t2.second_temp_lpn
AND t1.second_WL=t2.second_WL
AND t1.second_temp_lpn_validated=t2.second_temp_lpn_validated
AND t1.third_temp_lpn=t2.third_temp_lpn
AND t1.third_WL=t2.third_WL
AND t1.third_temp_lpn_validated=t2.third_temp_lpn_validated
AND t1.first_temp_rising=t2.first_temp_rising
AND t1.first_WR=t2.first_WR
AND t1.first_temp_rising_validated=t2.first_temp_rising_validated
AND t1.second_temp_rising=t2.second_temp_rising
AND t1.second_WR=t2.second_WR
AND t1.second_temp_rising_validated=t2.second_temp_rising_validated
AND t1.third_temp_rising=t2.third_temp_rising
AND t1.third_WR=t2.third_WR
AND t1.third_temp_rising_validated=t2.third_temp_rising_validated
AND t1.id<t2.id;
运行查询后我们收到的消息是：没有错误，0行受影响，耗时40.4秒。
英文:
We want to delete duplicated rows on our MySQL database, and we have tried a lot of queries, but for unfortunately we haven't succeeded yet. We found this query on several posts, but didn't work either:
DELETE t1 FROM Raw_Validated_backup AS t1 INNER JOIN Raw_Validated_backup AS t2
ON t1.time_start=t2.time_start
AND t1.time_end=t2.time_end
AND t1.first_temp_lpn=t2.first_temp_lpn
AND t1.first_WL=t2.first_WL
AND t1.first_temp_lpn_validated=t2.first_temp_lpn_validated
AND t1.second_temp_lpn=t2.second_temp_lpn
AND t1.second_WL=t2.second_WL
AND t1.second_temp_lpn_validated=t2.second_temp_lpn_validated
AND t1.third_temp_lpn=t2.third_temp_lpn
AND t1.third_WL=t2.third_WL
AND t1.third_temp_lpn_validated=t2.third_temp_lpn_validated
AND t1.first_temp_rising=t2.first_temp_rising
AND t1.first_WR=t2.first_WR
AND t1.first_temp_rising_validated=t2.first_temp_rising_validated
AND t1.second_temp_rising=t2.second_temp_rising
AND t1.second_WR=t2.second_WR
AND t1.second_temp_rising_validated=t2.second_temp_rising_validated
AND t1.third_temp_rising=t2.third_temp_rising
AND t1.third_WR=t2.third_WR
AND t1.third_temp_rising_validated=t2.third_temp_rising_validated
AND t1.id<t2.id;
Message we receive after running query: No errors, 0 rows affected, taking 40,4 s
答案1
得分: 2
这个查询：
select max(id) id
from Raw_Validated_backup
group by <除了id之外的所有列的列表>
返回你想要保留的行的所有id。<br/>所以删除其余的：
delete from Raw_Validated_backup
where id not in (
select t.id from (
select max(id) id
from Raw_Validated_backup
group by <除了id之外的所有列的列表>
) t
)
查看demo。<br/>另一个选项是使用自连接：
delete v1
from Raw_Validated_backup v1 inner join Raw_Validated_backup v2
on v1.time_start = v2.time_start and v1.time_end = v2.time_end and .......
and v1.id < v2.id;
查看简化的demo。
英文:
This query:
select max(id) id
from Raw_Validated_backup
group by <list of all the columns except id>
returns all the ids for the rows that you want to keep.<br/>
So delete the rest:
delete from Raw_Validated_backup
where id not in (
select t.id from (
select max(id) id
from Raw_Validated_backup
group by <list of all the columns except id>
) t
)
See the demo.<br/>
Another option with a self join:
delete v1
from Raw_Validated_backup v1 inner join Raw_Validated_backup v2
on v1.time_start = v2.time_start and v1.time_end = v2.time_end and .......
and v1.id < v2.id;
See a simplified demo.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论