sqlite 删除表中重复数据

180it 2019-10-22 PM 923℃ 0条

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 号码);

支付宝打赏支付宝打赏 微信打赏微信打赏

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

标签: none

sqlite 删除表中重复数据