SQL找出重复数据只保留一条
来源:互联网 发布:uk域名 编辑:程序博客网 时间:2024/06/05 19:01
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
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 peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)
3、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
6.消除一个字段的左边的第一位:
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
7.消除一个字段的右边的第一位:
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
8.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId
select *from r_people_report_bak ppp where 1=1and length(ppp.reporting_period)<=6and ppp.organization_code || '_' ||to_char(to_date(ppp.reporting_period ,'YYYY-mm'),'yyyy') || '-' || to_char(to_date(ppp.reporting_period ,'YYYY-mm'),'mm')|| '_' || ppp.datilor_allin (select pp.organization_code || '_' || pp.aa || '_' || pp.datilor_all from (select p.people_id,p.organization_code,p.datilor_all,to_char(to_date(p.reporting_period ,'YYYY-mm'),'yyyy') || '-' || to_char(to_date(p.reporting_period ,'YYYY-mm'),'mm') aafrom r_people_report_bak p where p.reporting_period is not null ) ppgroup by pp.aa ,pp.organization_code,pp.datilor_allhaving count(1) > 1 )
- SQL找出重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- SQL删除重复数据只保留一条
- java的Runtime的exec
- HDU 1576 A/B
- linux和windows文件名长度限制
- 根据时间自动调整屏幕亮度和颜色的软件f.lux
- Nginx+tomcat配置集群负载均衡
- SQL找出重复数据只保留一条
- Java中的数据类型
- GPU渲染相关文章
- ucControl控件自适应
- 第一个Java程序
- 读取 calss文件
- antlr demo
- IIS 7 ‘Handler "XXX" has a bad module "XXX" in its module list’
- Javac编译器源代码分析