1、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from t_usersroles a
where (a.roleid,a.userid) in (select roleid, userid from t_usersroles group by roleid, userid having count(*) > 1)
and rowid not in (select min(rowid) from t_usersroles group by roleid, userid having count(*)>1)
在几千条记录里,存在着些相同的记录,如何能用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
|
分享到:
相关推荐
常用Oracle语句,从零开始··常用Oracle语句,从零开始··
ORACLE经典常用语句总结 ORACLE经典常用语句总结
NULL 博文链接:https://jason-zhu.iteye.com/blog/448158
Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理 Oracle常用sql语句整理
Oracle学习,常用sql语句汇总,适合新手使用。
常用Oracle管理语句常用Oracle管理语句常用Oracle管理语句常用Oracle管理语句
一些常用到的在oracle中的sql语句,也是我在网上找到的,分享给大家
常用导入导出oracle语句
oracle常用sql语句大全 注释完整 放心使用 包含所有常用语句
Oracle常用SQL查询语句,Oracle常用SQL查询语句,Oracle常用SQL查询语句
oracle常用SQL语句下载 oracle常用SQL语句下载 oracle常用SQL语句下载
Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句Oracle常用脚本与语句...
oracle常用sql语句 oracle常用sql语句 oracle常用sql语句 oracle常用sql语句 oracle常用sql语句
经典SQL语句收集,在oracle中进行列行转换 ,五种提高SQL性能的方法,常用SQL查询,SQL优化原则等共计112册适合初学者学习。
oracle 常用语oracle 常用语oracle 常用语句
ORACLE常用SQL语句大全.pdf
oracle实施、维护人员常用的oracle语句集合。
Oracle维护常用SQL语句汇总
oracle数据库常用操作语句,实现对用户的管理和权限管理。
Oracle10G常用维护语句,很常用的。