筛选或者删除数据表中重复的数据

1.筛选重复的记录

http://stackoverflow.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://stackoverflow.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
         )

 

作者:Chuck Lu    GitHub