Group By

数据表说明

CREATE TABLE [FileList](
    [ProjectId] INT NOT NULL ON CONFLICT FAIL, 
    [SensorId] VARCHAR(50) NOT NULL ON CONFLICT FAIL, 
    [FileName] VARCHAR(50) NOT NULL ON CONFLICT FAIL, 
    [StartTime] DATETIME NOT NULL, 
    [EndTime] DATETIME NOT NULL, 
    [FileStatus] VARCHAR(20) DEFAULT Uncompleted, 
    [RowId] INTEGER PRIMARY KEY AUTOINCREMENT, 
    [Id] INT NOT NULL)

RowId作为主键，

用projectid,sensorid,filename三个字段作为记录是否重复的判断 

(projectid和sensorid，可以定位到id，所以也可以用id和filename作为记录是否重复的判断)

 

1.筛选全部数据

select * from filelist

显示有1854条数据

 

2.分组筛选，过滤掉重复的数据(重复的数据仅显示一条)

select * from filelist
group by projectid,sensorid,filename

--group by id,filename

 

显示有1552条数据，说明重复数据个数1854-1552=302

 

3.筛选出单条重复的数据(仅显示一条)

select * from filelist
group by id,filename
having count(*) > 1

显示有302条数据

 

重复数据，筛选出rowid较小的

select *
,min(rowid) 
from filelist
group by id,filename
having count(*) > 1

 

4.筛选出所有的重复的数据

select a.*,b.[count] from filelist as a
inner join
(select *,count(*) as count from filelist
group by id,filename
having count(*) > 1) as b
on 
a.id = b.id
and 
a.filename = b.filename

显示有604条数据

 

5.

作者:Chuck Lu    GitHub