在本文中,我們主要介紹重復字段的內容,自我感覺有個不錯的建議和大家分享下

????查詢及刪除重復記載的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 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)

????注:rowid為

???? Oracle

????自帶不必該.....

????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)

????(二)

????比方說在A表中存在一個字段“name”，而且不同記載之間的“name”值有可能會雷同，

????現在就是需要查詢出在該表中的各記載之間，“name”值存在重復的項；

????Select Name,Count(*) From A Group By Name Having Count(*) > 1

????如果還查性別也雷同大則如下:

????Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

????(三)

????方法一

????declare @max integer,@id integer

????declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1

????open cur_rows

????fetch cur_rows into @id,@max

????while @@fetch_status=0

????begin

????select @max = @max -1

????set rowcount @max

????delete from 表名 where 主字段 = @id

????fetch cur_rows into @id,@max

????end

????close cur_rows

????set rowcount 0

????方法二

????＂重復記載＂有兩個意義上的重復記載，一是完全重復的記載，也即全部字段均重復的記載，二是部分關鍵字段重復的記載，比如Name字段重復，而其他字段不一定重復或都重復可以疏忽。

????1、對于第一種重復，比擬容易解決，應用

????select distinct * from tableName

????就能夠得到無重復記載的結果集。 如果該表需要刪除重復的記載（重復記載保留1條），可以按以下方法刪除

????select distinct * into #Tmp from tableName

????drop table tableName

????select * into tableName from #Tmp

????drop table #Tmp

????發生這種重復的原因是表設計不周發生的，增加獨一索引列便可解決。

????2、這類重復問題平日要求保留重復記載中的第一條記載，操作方法如下

????假設有重復的字段為Name,Address，要求得到這兩個字段獨一的結果集

????select identity(int,1,1) as autoID, * into #Tmp from tableName

????select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID

????select * from #Tmp where autoID in(select autoID from #tmp2)

????最后一個select即得到了Name，Address不重復的結果集（但多了一個autoID字段，現實寫時可以寫在select子句中省去此列）

????(四)

????查詢重復

????select * from tablename where id in (

????select id from tablename

????group by id

????having count(id) > 1)

????=======================================

????1。用rowid方法

????據據Oracle帶的rowid屬性，進行判斷，是否存在重復,語句如下：

????查數據:

????select * from table1 a where rowid !=(select??max(rowid)?

????from table1 b where a.name1=b.name1 and a.name2=b.name2......)

????刪數據：

????delete??from table1 a where rowid !=(select??max(rowid)?

????from table1 b where a.name1=b.name1 and a.name2=b.name2......)

????2.group by方法

????查數據:

????select count(num), max(name) from student --列出重復的記載數，并列出他的name屬性

????group by num

????having count(num) >1 --按num分組后找出表中num列重復，即出現次數大于一次

????刪數據：

????delete from student

????group by num

????having count(num) >1

????這樣的話就把全部重復的都刪除了。

????3.用distinct方法 -對于小的表比擬有效?

????create table table_new as??select distinct *??from table1 minux

????truncate table table1;

????insert into table1 select * from table_new;

文章結束給大家分享下程序員的一些笑話語錄： 手機終究會變成PC，所以ip會比wm更加暢銷，但是有一天手機強大到一定程度了就會發現只有wm的支持才能完美享受。就好比樹和草，草長得再高也是草，時間到了條件成熟了樹就會竄天高了。www.ishuo.cn

重復字段Oracle刪除重復行