sqlite 删除表中重复数据

sqlite 删除表中重复数据（亲测可用）

例子：表名 Paper 。通过字段PaperID查找重复数据。

1 --查询某表中重复的数据

方法1   select * from Paper group by PaperID having count(*)>1;


 方法2   SELECT 去重字段

FROM 表名 tempTable
WHERE (tempTable.去重字段) IN (

       SELECT 去重字段
         FROM 表名
        GROUP BY 去重字段
       HAVING count(*)>= 2
        ORDER BY 去重字段
   ); 

2--删除重复行数，只剩不重复的记录（rowid为sqlite自带字段）

  delete from Paper where Paper.rowid not in (select MAX(Paper.rowid) from Paper group by PaperID);

自己留着备忘：
SELECT id, "号码","密码"
FROM [User]
WHERE ("号码") IN (

       SELECT 号码
         FROM [User]
        GROUP BY "号码"
       HAVING count( * ) >= 2
        ORDER BY "号码"
   );

SqLite根据某一个字段查询数据表中重复数据记录的SQL语句如下：
SELECT 号码 FROM 表名 GROUP BY 号码 HAVING COUNT(号码)>1 ORDER BY COUNT(号码) DESC
SqLite根据某一个字段删除数据表中重复数据记录的SQL语句如下：
delete from 表名 where id not in (select min(id) from 表名 group by 号码);

赏 支付宝打赏 微信打赏

如果文章或资源对您有帮助，欢迎打赏作者。一路走来，感谢有您！