1.筛选重复的记录
http://*.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database
select s.*,t.[count]
from filelist as s
inner join (
select ProjectId,SensorId, FileName, count(*) as [count]
from filelist
group by SensorId, FileName
having count(*) > 1
) as t
on s.ProjectId = t.ProjectId and s.SensorId = t.SensorId and s.FileName = t.FileName
需要注意的是group by筛选出来的数据,只是重复数据中的1条
If you want to find duplicate data (by one or several criterias) and select the actualrows.
This should also work, maybe give it try. SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;
I think this will work properly to search repeated values in a particular column.
SELECT gid_Sensorinfo, COUNT(gid_Sensorinfo) FROM rel_testareasensor GROUP BY gid_Sensorinfo HAVING COUNT(gid_Sensorinfo)>1;
Example
列出重复的记录,以及每一个记录重复了多少次
SELECT ViewName , COUNT(ViewName) AS [Count] FROM dbo.tbm_vie_View GROUP BY ViewName HAVING COUNT(ViewName) > 1;
筛选出重复的数据,可以包含重复数据的所有列
SELECT ViewID , DynamicEntityID , ViewName , ViewDescription FROM tbm_vie_View WHERE ViewName IN ( SELECT ViewName FROM dbo.tbm_vie_View GROUP BY ViewName HAVING COUNT(ViewName) > 1 );
2.删除重复的记录
http://*.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database
You need a way to distinguish the rows. Based on your comment, you could use the special rowid column for that.
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 )