从sqlite数据库中删除重复的行 [英] Deleting duplicate rows from sqlite database

问题描述

在SQLite3中有一个巨大的表 - 3600万行。

I have a huge table - 36 million rows - in SQLite3.

在这个非常大的表中，有两列

In this very large table, there are two columns

• 哈希 - 文本


• d - 真实

但是，某些行是重复的。也就是说，hash和d都具有相同的值。

However, some of the rows are duplicates. That is, both hash and d have the same values.

此外，如果两个散列相同，d的值也是相同的，但是两个相同的ds不意味着两个相同的散列

Also, if two hashes are identical, so are the values of d, but two identical ds does not imply two identical hashes

无论如何，我想删除重复的行。我没有主键列，因为我是一个白痴。

Anyway, I want to delete the duplicate rows. I don't have a primary key column because i'm an idiot. What's the fastest way to do this?

编辑： delete from dist where rowid not in从dist组中选择max（rowid）by hash）;

看起来要做的。

推荐答案

您需要一种方法来区分行。根据您的评论，您可以使用特殊的 rowid列。

You need a way to distinguish the rows. Based on your comment, you could use the special rowid column for that.

通过保留最低 rowid 每（hash，d）删除重复项：

To delete duplicates by keeping the lowest rowid per (hash,d):

delete   from YourTable
where    rowid not in
         (
         select  min(rowid)
         from    YourTable
         group by
                 hash
         ,       d
         )

这篇关于从sqlite数据库中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！