如何删除 SQLite 中的重复项? [英] how can I delete duplicates in SQLite?

问题描述

我有一个 SQLite 数据库，其中语句:

I have a SQLite DB where the statement:

SELECT messdatum, count(*) as anzahl 
from lipo 
GROUP BY Messdatum 
ORDER BY anzahl desc;

导致某些行，这表明我有一些具有相同Messdatum 的重复项.如何仅从我的 sqlite 数据库中删除重复项?(它应该删除信息相同的 anzahl-1 记录?)有没有人有建议?

results in some lines, which indicates that I have some duplicates with the same Messdatum. How can I delete the duplicates only form my sqlite db? (it should delete anzahl-1 records where the messdatum is the same?) Has anyone an advice?

PS:我找到了这个链接如何从 Microsoft 中删除重复项，但有问题sqlite 方言.由于 sqlite 语法，我遇到了一些错误.所以 f.e.我可以这样做:

PS: I found this link How to remove duplicate from Microsoft but have problems with sqlite dialect. I got some errors due to the sqlite syntax. So f.e. I could do:

 INSERT into holdkey SELECT messdatum, count(*) as anzahl from lipo group by messdatum having count(*) > 1;

 INSERT into holddups SELECT DISTINCT lipo.* from lipo, holdkey where lipo.Messdatum = holdkey.messdatum ;

 DELETE lipo from lipo, holdkey where lipo.messdatum = holdkey.messdatum;

这是删除命令的错误.我该怎么做?我尝试使用

here is an error at the delete command. How can I do that? I tried to update the holdkey.anzahl to an additional col in lipo with

UPDATE lipo,holdkey set lipo.duplettenzahl = holdkey.anzahl WHERE lipo.messdatum = holdkey.messdatum ; 

但这也是不可能的.如果我将 anzahl 作为 lipo 中的 dublettenzahl，我可以从 lipo 中删除 dublettenzahl > 0 的所有记录.请帮忙！谢谢

but this is also not possible. If I would have the anzahl as dublettenzahl in lipo I could delete all records from lipo where dublettenzahl > 0. Please help! Thanks

推荐答案

SQLite 有一个 特殊列，ROWID 默认在每个表上创建(您可以将其关闭 使用 WITHOUT ROWID 修饰符，但在这样做之前要非常确定).

SQLite has a special column, ROWID created on every table by default (you can switch it off using the WITHOUT ROWID modifier, but be very sure before doing so).

这意味着我们可以识别重复组中的特定行，例如，查找值的第一个条目:

This means that we can identify specific rows within sets of duplicates, for example, finding the first entry for a value:

SELECT messdatum, MIN(ROWID) FROM lipo

因此删除重复项的一种方法可能是:

So one way to remove duplicates might be this:

DELETE FROM lipo
WHERE rowid NOT IN (
  SELECT MIN(rowid) 
  FROM lipo 
  GROUP BY messdatum
)

这篇关于如何删除 SQLite 中的重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！