数据库删除记录:数据库删除完全重复和部分关键字段重复的记录来源: 发布时间:星期三, 2008年12月24日 浏览:147次 评论:0
1、第种重复很容易解决区别数据库环境下思路方法相似:
以下为引用内容: Mysql create table tmp select distinct * from tableName; drop table tableName; create table tableName select * from tmp; drop table tmp; SQL Server select distinct * o #Tmp from tableName; drop table tableName; select * o tableName from #Tmp; drop table #Tmp; Oracle create table tmp as select distinct * from tableName; drop table tableName; create table tableName as select * from tmp; drop table tmp; 发生这种重复原因是由于表设计不周而产生增加唯索引列就可以解决此问题 2、此类重复问题通常要求保留重复记录中第条记录操作思路方法如下 假设有重复字段为Name,Address要求得到这两个字段唯结果集 Mysql 以下为引用内容: alter table tableName add autoID auto_increment not null; create table tmp select min(autoID) as autoID from tableName group by Name,Address; create table tmp2 select tableName.* from tableName,tmp where tableName.autoID = tmp.autoID; drop table tableName; rename table tmp2 to tableName; SQL Server select identity(,1,1) as autoID, * o #Tmp from tableName; select min(autoID) as autoID o #Tmp2 from #Tmp group by Name,Address; drop table tableName; select * o tableName from #Tmp where autoID in(select autoID from #Tmp2); drop table #Tmp; drop table #Tmp2; Oracle DELETE FROM tableName t1 WHERE t1.ROWID > (SELECT MIN(t2.ROWID) FROM tableName t2 WHERE t2.Name = t1.Name and t2.Address = t1.Address); 介绍说明: 1. MySQL和SQL Server中最后个select得到了NameAddress不重复结果集(多了个autoID字段在大家实际写时可以写在select子句中省去此列) 2. MySQL和SQL Server没有提供rowid机制所以需要通过个autoID列来实现行唯性而利用Oraclerowid处理就方便多了而且使用ROWID是最高效删除重复记录思路方法 0
相关文章读者评论发表评论 |