SQL查询多字段重复项（只保留一个），并进行统计

Question

如有某数据库如下：

fldBeginT、fldQuserC、fldUseTim、fldDate如有相同只保留1条，并按fldQuserC进行统计有多少条。

请大神解答。

Answer 1

我在网站上帮你找的答案！如下`

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1) 
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1) 

简单的说第一个就是删除所有重复的数据，第二个是删除重复数据的同时保留一条数据。它运用的是oracle。

如果是mysql的话，请M我